Question 240476 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Why Is my script not exporting data on Form Submit

first time posting something here so sorry if I make any mistakes.

I wanted to transfer data from Google Forms to Google Sheet, I did get the Google Sheet but I also need to export that data to another Sheet (Target).

I am not at all competent with Java Script so I asked Chat GPT about solutions without using =IMPORTRANGE as I thought that it would be inefficient when the new data gets added to the answers of Google Forms.

It gave me the following answer I changed the name of the sheet and the Sheet ID accordingly but the code still didn’t work and it didn’t specify a reason.(The feedback I got from Apps Script was: An unknown error has occured. Please try agaim.)

function onFormSubmit(e) {
 var targetSpreadsheetId = 'YOUR_TARGET_SPREADSHEET_ID'; // Replace with the target spreadsheet's ID
 var targetSheetName = 'Target'; // Replace with the target sheet's name

 var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
 var targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
  
 var valuesToCopy = e.values;
 targetSheet.appendRow(valuesToCopy);
}

So I am asking all the JS, Excel and App Script gods is there another way for me to import the data to different sheets, and make it so that when new input is given it transfers automatically. Or what went wrong with this code and hwo should I fix it?

Thanks everyone in advance for reading and answering :*

Tags:

2

Answers


  1. 0

    Not sure why your script is not working. Did you set up a trigger? As an alternative, this should work for you:

    function copyFormSubmission(event) {  
  var target = SpreadsheetApp.openById('SheetID').getSheetByName('SheetName');
  target.appendRow(event.values);
}

    Then set up a simple trigger in the Script Editor using "On form submit". Triggers can be found on the left side in the Script Editor:
    Trigger
    Screenshot

    With this code the data can be transferred to another sheet in the same spreadsheet project or to another spreadsheet. Just use the appropriate Sheet ID and Sheet Name.

    Login or Signup to reply.
  2. 0

    This works for me:

    function onFormSubmit1(e) {
 var targetSpreadsheetId = gobj.globals.ssid; // Replace with the target spreadsheet's ID
 var targetSheetName = 'Sheet2'; // Replace with the target sheet's name

 var targetSpreadsheet = SpreadsheetApp.openById(gobj.globals.test2id);
 var targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
 Logger.log(e.values) 
 targetSheet.appendRow(valuesToCopy);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search