CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
Edin Mehmedagic
Edin Mehmedagic

Reputation: 1

Copying last form response to new last row other sheet

I am very new to coding with apps script but I am trying to set up a code that transfer the last row of a new google forms spreadsheet submission to another sheet in the same workbook. The reason I am trying to do this is so I can make simple calculations with the data and also edit the data (you can't edit the data with Query functions).

I have one sheet called (Form Responses 1) and one sheet called (Clients). When the form is filled out all the answers are ofcourse automatically transferred to the Form Response 1 sheet. I would like to automatically copy the last row of the Form Response 1 sheet to the Clients sheet so the information can be edited and used in calculations without losing the original submission which will be in Form Responses 1 (not a query).

There are two of the codes I tried with my very basic knowledge and they both do not work. The codes probably suck but any help and advice is greatly appreciated!

Code 1 that I tried

function onFormSubmit(e){
  var ss = SpreadsheetApp.getSheetByName("Form responses 1")();
  var copyFromRange = 'Form responses 1!A2:M999';
  var copyToRangeStart = 'Clients!A2:M999';
  copyValuesOnly(copyFromRange, copyToRangeStart);
}

Code 2 that I tried

function onFormSubmit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Form responses 1');
  var r = s.getRange(s.getLastRow(),s.getLastColumn());
  var data = r.getValues();

  var row = r.getRow();
  var numColumns = s.getLastColumn();
  var targetSheet = ss.getSheetByName("Clients");
  var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
  targetSheet.getRange(targetSheet.getLastRow()+1,1,1,20).setValues(data);

I have been browsing the website while trying to figure this out and learn more on scripting, you have all been a great help!

Upvotes: 0

Views: 761

Answers (1)

Cooper
Cooper

Reputation: 64062

function onFormSubmit(e){
  var ss=SpreadsheetApp.getActive();
  ss.getSheetByName('Clients').appendRow(e.values);
}

onFormSubmit Event Object

You don't want to get the last row because if submissions come to fast the last row may not be the one from the currrent onFormSubmit trigger.

Upvotes: 0

Related Questions