google apps script - Copy data to a separate file when submitted

Question

I'm wondering if when submitted, the data can be pasted to another separate file (and not tab). Do you think that there's a way?

You can access the file here : https://docs.google.com/spreadsheets/d/1FFlsfFyVyQobnbfAgQZA_r5IQeBhgOdK4cBpi5yo77U/edit?usp=sharing

Following code:

function myFunction(){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inputS = ss.getSheetByName("Input");
  var outputS = ss.getSheetByName("Output");
  var inputRng = inputS.getRange("A2:I2");
  var values = inputRng.getValues();
  inputRng.clearContent(); // clear input range       
  outputS.getRange(outputS.getLastRow()+1, 1, 1, values[0].length).setValues(values);
}

Answer 1

Explanation:

You can use openById(id) to access a different spreadsheet file by its id. As the documentation states:

For example, the spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0 is "abc1234567".

and assuming that the target spreadsheet has a sheet named "Output" then the following code will work. Otherwise, change it to the name of the target sheet.

Solution:

function ScriptBS() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ts = SpreadsheetApp.openById(id); // put here the id of the target spreadsheet file
  var inputS = ss.getSheetByName("Input");
  var outputS = ts.getSheetByName("Output"); // get sheet Output of the target spreadsheet file
  var inputRng = inputS.getRange("A2:I2");
  var values = inputRng.getValues();
  inputRng.clearContent(); // clear input range       
  outputS.getRange(outputS.getLastRow()+1, 1, 1, values[0].length).setValues(values);
}