Auto Copying Rows in Google Sheets

Question

I'm attempting to write a few functions that

copy google form submission to from "form submission" sheet to "All Leads" sheet
move whole row from "All Leads" sheet to "Open Leads" sheet based on a cell value = "open"
move whole row data either back to "All Leads" or "closed","lost" sheets based on cell value ="closed/lost/blank"


 var sheetNameToWatch = "Form Responses";
 var columnNumberToWatch = 3;
 var sheetNameToMoveTheRowTo = "All Leads";

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getActiveCell();

 if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && isBlank(range) == FALSE) {
   var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
   var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
   sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
   //sheet.deleteRow(range.getRow());

 }

}

But I'm not getting this to trigger onEdit or onFormSubmission. Any help is appreciated, and as you can tell i'm new to scripting.

Here is the shared google spreadsheet

https://docs.google.com/spreadsheets/d/13090GHrXY17lRzA9ppQHZZGyT9yUNk6hMMXDQIKmddI/edit?usp=sharing

I cannot understand about I'm not getting this to trigger onEdit or onFormSubmission.. Can I ask you about the detail of it? And also, can I ask you about your current issue of your script? — Tanaike
Hey cooper thanks for getting back to me. Of course - in google scripts theres a 'create a trigger for this function to run' and through google forms specifically this one: forms.gle/6HvpLaotQSN7MhiQ7 - the information gets stored in sheet: 'form submission' - and i wanted to trigger the function 'on form submission' — relativeLee
To be a little more clear - i'm entering in client's data into that^ form in my previous comment. the data then gets added to sheet: 'form submission'. I want to copy the data to a new sheet word-for-word so the method i'm employing is - i'm trying to get it to check column 3:'timestamp' for isBlank(false) - and then copy that row to the new sheet. — relativeLee

ale13 ale13 · Accepted Answer · 2020-02-27T13:19:11

Since you already have entries stored in your Spreadsheet, you should just run this snippet once to copy the already stored values in their place. This works by gathering all the data wanted from the Form Responses sheet by using the getRange(row, col, numrows, numcols) method and then pasting the values by using the setValues() method.

function copyStuff() {
  var spreadsheet = SpreadsheetApp.openById("ID_OF_YOUR_SPREADSHEET");
  var allLeads = spreadsheet.getSheetByName("All Leads");
  var formSub = spreadsheet.getSheetByName("Form Responses");
  var valsFromForm = formSub.getRange(3, 1, 53, 13).getValues();
  allLeads.getRange(2, 1, 53, 13).setValues(valsFromForm);
}

Afterwards, you should replace the above snippet with this one:

function onFormSubmit() {
  var spreadsheet = SpreadsheetApp.openById("ID_OF_YOUR_SPREADSHEET");
  var allLeads = spreadsheet.getSheetByName("All Leads");
  var formSub = spreadsheet.getSheetByName("Form Responses");

  var openSheet = spreadsheet.getSheetByName("Open Leads");
  var closedSheet = spreadsheet.getSheetByName("Closed");
  var lostSheet = spreadsheet.getSheetByName("Lost");

  var lastVals = formSub.getRange(formSub.getLastRow(), 1, 1, 13).getValues();
  allLeads.getRange(allLeads.getLastRow(), 1, 1, 13).setValues(lastVals);


  var cellValue = spreadsheet.getSheetByName("THE_NAME_OF_YOUR_SHEET_WHERE_CELL_VALUE_IS").getValue();

  if (cellValue == "OPEN")
    openSheet.getRange(openSheet.getLastRow(), 1, 1, 13).setValues(lastVals);
  else if (cellValue == "CLOSED")
    closedSheet.getRange(closedSheet.getLastRow(), 1, 1, 13).setValues(lastVals);
  else if (cellValue == "LOST")
    lostSheet.getRange(lostSheet.getLastRow(), 1, 1, 13).setValues(lastVals);


  var lastVals = formSub.getRange(formSub.getLastRow(), 1, 1, 13).getValues();
  allLeads.getRange(allLeads.getLastRow(), 1, 1, 13).setValues(lastVals);
}

The above snippet works by getting the last row from the Spreadsheet and then pasting it in the right place based on the cellValue. To get the last row of the sheet, the getLastRow() method has been used.

Since you want this to run on every form submission, you should use an onFormSubmit() installable trigger, and configure it like this:

Note: You will have to have the script linked to the form.

Reference

Auto Copying Rows in Google Sheets

1 Answers