Google-sheets – Automatically copy and paste cells that have just been updated to a list from forms

google sheetsgoogle-apps-scriptgoogle-forms

I have a spreadsheet that collects information from two different forms. Each form feeds a different sheet in the spreadsheet. There are 3 other sheets that the info from either of the first two forms will be assigned to. I would like to have a couple things happen but it's really the same function for it all.

  1. I need it to watch the "incoming" sheets list and copy and paste the rows to the last row on another sheet based on the value of cell in column B (this is the event that should decide when and where to copy and paste the info). Eg: if onEdit col B = Luke then copy and paste row to sheet "Luke"

  2. I also need to auto fill a different form with certain cells values if say column k says "Yes".

I currently have a query moving the info to the sheets however I then have to manually copy and paste all the values over so they can be updated on their respective sheets. I am new to scripting and am having trouble tracking down the needed codes and the variables they require. I noticed that when people use onEdit for instance they always set var ss = getActiveSheet is there a place where I can read up on how these variables work and what they require?

Best Answer

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

  1. ss = active spreadsheet, sheet = active sheet
  2. values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
  3. row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
  4. values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}
Related Topic