Form data submit to external sheet (google script)-CodePudding

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();


    }






}

CodePudding user response：

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: