Google-sheets – How to paste a cell range automatically – Values only

google sheets

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.

Best Answer

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.

Related Topic