Event values and offset in Google spreadsheet

Question

I had fantastic help from @Mogsdad on another issue here:

Copy last row to another spreadsheet upon form submission

function copyLastRow(event) {  
  var target = SpreadsheetApp.openById('xxxxxxxxx').getSheetByName('Sheet1');
  target.appendRow(event.values);
}

This script works extremely well - it copies the last added row from the original source with form responses to another spreadsheet. It copies all the values from the form, but on the original spreadsheet with responses I have an additional column which is not part of the submission form. I need to copy data from this column as well. I had a reply from Mogsdad which says that the event object includes the range that has been updated, so I should use that with offset to grab the additional cell(s). I searched everywhere for any examples with eventvalues and offset, but failed to find anything. I would very much appreciate any help with this as I don't have much experience. Thanks.

Mogsdad Mogsdad · Accepted Answer · 2014-10-21T18:29:47

The Event object includes a range property that tells us where the latest form response has been placed in the sheet. All methods for Class Range can be applied to this range. For example, .getWidth() will return the range width, giving us a starting point for expanding the range to copy.

Since we want to get the cells from the response plus one (or more) additional columns from the sheet, we can use the offset() method to expand the original range. (Note that offset() is a range method - it does not apply to the event.values array.)

function copyLastRow(event) {  
  var target = SpreadsheetApp.openById('xxxxxxxxx').getSheetByName('Sheet1');
  var sourceWidth = event.range.getWidth() + additionalColumns; // define a value here
  // Grab all the cells in this response, plus additionalColumns
  var sourceRange = event.range.offset(0,0,1,sourceWidth);
  target.appendRow(sourceRange.getValues()[0]);
}

Note that because we use the information from the event object, this function remains tolerant to multiple form submissions.

Event values and offset in Google spreadsheet

1 Answers