2

I need some "live" cell data (values only), which get their data from another sheet; an arrayformula is not sufficient.

I'm thinking something that copies column A:13-down from Sheet1 (whenever it changes), and then pastes the values, beginning at row 3 of column A on Sheet2.

This answer looks like a starting point for me but lacks any mention of doing it automatically when the contents change.

Can anyone help me out?


EDIT: Thanks @Tom, my implementation of the script looks like this:

/**
 * A function named onEdit will be called whenever
 * a change is made to the spreadsheet.
 * 
 * @param  {object} e The edit event (not used in this case)
 */
function onEdit(e){
  var copyFromRange = 'ReportCalculations!A1:E39';
  var copyToRangeStart = 'Report!A1';
  copyValuesOnly((copyFromRange, copyToRangeStart));
}

/**
 * This function will copy the values from a given range to
 * a second range, which starts from the given cell reference
 * 
 * @param  {string} copyFromRange    Range reference eg: 
 * @param  {string} copyToRangeStart Cell reference eg:
 */
function copyValuesOnly(copyFromRange, copyToRangeStart) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange(copyFromRange);
  source.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
}

At this point when the ReportCalculations sheet is edited, I'm still left with an empty Report sheet – must be missing something

My steps so far:

  • Created the new script and saved it
  • Tried editing the sheet (nothing)
  • Then tried manually running the script from the script manager, which asked me for authorization (which I gave), then tried editing the ReportCalculations sheet again and still nothing in the Report sheet.
1
  • Perhaps you can share a doc with us and show us the expected outcome? Sep 30, 2013 at 13:37

1 Answer 1

4

You could use an onEdit trigger for the script. That will update whenever changes are made. For example:

/**
 * A function named onEdit will be called whenever
 * a change is made to the spreadsheet.
 * 
 * @param  {object} e The edit event (not used in this case)
 */
function onEdit(e){
  var copyFromRange = 'Sheet1!A13:A'; // no row for second cell reference
  var copyToRangeStart = 'Sheet2!A3';
  copyValuesOnly(copyFromRange, copyToRangeStart);
}

/**
 * This function will copy the values from a given range to
 * a second range, which starts from the given cell reference
 * 
 * @param  {string} copyFromRange    Range reference eg: 
 * @param  {string} copyToRangeStart Cell reference eg:
 */
function copyValuesOnly(copyFromRange, copyToRangeStart) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange(copyFromRange);
  source.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
}

See https://developers.google.com/apps-script/understanding_triggers for more details on triggering scripts automatically.

I can't comment yet, so will ask here: Do you need to copy to a second spreadsheet or just a second sheet within the same spreadsheet? The second case is answered above, but I can adjust if you need to copy to a separate spreadsheet?

edit: added code to fully answer the question. Thanks for the guidance Jacob.

5
  • I think it was just an error with quotes ... thanks @Tom trying the script now
    – Drewdavid
    Oct 7, 2013 at 17:27
  • Hi, ok I created the new script, edited the source sheet, but nothing has happened. It did ask for authorization for the onEdit function when I tried to manually run the script using the script manager. What am I missing? Thanks :)
    – Drewdavid
    Oct 7, 2013 at 17:39
  • @Drewdavid: Could you please add a link to the spreadsheet so we can have a look? Oct 7, 2013 at 21:17
  • My apologies @Drewdavid - the line copyValuesOnly((copyFromRange, copyToRangeStart)); should have read copyValuesOnly(copyFromRange, copyToRangeStart); The extra parentheses were causing the fault. I have changed it in the answer and your Example sheet and it works now. Best of luck! Oct 9, 2013 at 13:33
  • Hi, just wanted to say this is excellent. Thank You very much for your help, and for tracking with me on it to completion :)
    – Drewdavid
    Oct 10, 2013 at 0:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.