Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

As requested, here's a new, editable spreadsheet with some placeholder data http://goo.gl/IzXRUl

share|improve this question
    
Perhaps you can share a doc with us and show us the expected outcome? –  Jacob Jan Tuinstra Sep 30 '13 at 13:37
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
I think it was just an error with quotes ... thanks @Tom trying the script now –  Drewdavid Oct 7 '13 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 '13 at 17:39
    
@Drewdavid: Could you please add a link to the spreadsheet so we can have a look? –  Tom Horwood Oct 7 '13 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! –  Tom Horwood Oct 9 '13 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 '13 at 0:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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