Form data submit to external sheet (google script)

Question

I have created a data entry form in google sheets, now I am having trouble saving said entry to an external sheet so far this is what I have, I do not know if I am using the right method to reference sheet.

please look at comments, thank you

Everytime I am trying to submit the form it is giving lastRow Null

function submitData() {

  //variable declaration for referenceing the active google sheet

  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shUserForm = myGoogleSheet.getSheetByName('MasterSheet');

  //open external sheet
   var extSS = SpreadsheetApp.openById("1U9SmfPCH9v8maqh6XL0dmRL85TJZfckUV7SAEGM3pQo");
  var datasheet= extSS.getSheetByName("Mastersheet");

  var srcData = shUserForm.getDataRange().getValues();
 

  //to create the instance of the ui environment to use the alert features


  var ui = SpreadsheetApp.getUi();

  var response = ui.alert("Submit", "Do you want to submit the data?", ui.ButtonSet.YES_NO);


  //checking the user response

  if ( response == ui.NO) {

  return; // to ext from this function

  }

  if ( validateEntry()== true) {

    var blankRow =datasheet.getLastRow() + 1; //identify the next blank row

    //'code to update the database sheet ( write data entry to DB sheet)

    datasheet.getRange(blankRow,1).setValue(srcData.getRange("C7").getValue()); //input number
    datasheet.getRange(blankRow,2).setValue(srcData.getRange("C9").getValue()); //Department
    datasheet.getRange(blankRow,3).setValue(srcData.getRange("C11").getValue()); //Agent Name
    datasheet.getRange(blankRow,4).setValue(srcData.getRange("C13").getValue()); //Hub location
    datasheet.getRange(blankRow,9).setValue(srcData.getRange("C15").getValue()); //order ID
    datasheet.getRange(blankRow,10).setValue(srcData.getRange("C20").getValue()); //type of entry
    datasheet.getRange(blankRow,5).setValue(Session.getActiveUser().getEmail()); //Submitted By, this will automatically get user email
    datasheet.getRange(blankRow,7).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm:');
    datasheet.getRange(blankRow,8).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm:');
    datasheet.getRange(blankRow,6).setValue(srcData.getRange("C17").getValue()); //comments


    ui.alert(' "New Data Saved - Input #' + shUserForm.getRange("C7").getValue() + '"');

    shUserForm.getRange("C7").clear();
    shUserForm.getRange("C9").clear();
    shUserForm.getRange("C11").clear();
    shUserForm.getRange("C13").clear();
    shUserForm.getRange("C15").clear();
    shUserForm.getRange("C17").clear();


    }






}

Nikko J. · Accepted Answer · 2021-11-22 19:37:52Z

2

To save data to another Spreadsheet, You need to open it first by using either openById("insert sheet id here") or openByUrl("insert sheet URL here"). This will return a Spreadsheet class which has getSheetByName("sheet name") method you can use to access the Sheet.

ID of the spreadsheet can be found in the url. This can be found after /d/ or before /edit

Example ID:

https://docs.google.com/spreadsheets/d/12345/edit#gid=0

12345 is the Spreadsheet ID

Example:

Code:

function saveData() {
  //open current sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Sheet1");

  //open external sheet
  var extSS = SpreadsheetApp.openById("Insert External Spreadsheet ID here");
  var extSH = extSS.getSheetByName("Sheet1 External");

  //get data from current Sheet.
  var srcData = sh.getDataRange().getValues();

  //write to external sheet
  extSH.getRange(extSH.getLastRow()+1, 1, srcData.length, srcData[0].length).setValues(srcData);
}

This script will paste the values from Sheet1 to Sheet2

Before:

Sheet1 Data:

Sheet2 Data:

After:

Sheet1 Data:

Sheet2 Data:

edited Nov 22, 2021 at 19:37

answered Nov 22, 2021 at 18:56

Nikko J.

5,5531 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jove Over a year ago

do I need to declare an activesheet ?

Nikko J. Over a year ago

@Jove - Do you mean getActiveSpreadsheet or getActiveSheet?

Jove Over a year ago

Hi! I have implemented your code however, it's giving me this cannot read property getLastRow() of null...., I edited the post above, do you mind please if you can check if I am doing the right thing? this will be very helpful

Nikko J. Over a year ago

getLastRow() is a method of class Sheet. It can be access by opening a Sheet using getSheetByName() method. In my example it's the var extSH = extSS.getSheetByName("Sheet1 External");. The content of var extSH is a Class Sheet for Sheet2. Could you edit your post and append your current code? I will try to help you debug.

Nikko J. Over a year ago

@Jove - See my updated answer

|

Collectives™ on Stack Overflow

Form data submit to external sheet (google script)

1 Answer 1

Example:

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Example:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related