Google Sheets Script Not Outputting to Datasheet-CodePudding

I'm currently having an issue with this simple script that basically just copies and pastes input data. The function inputting the value to the Sales Log tab but not the Pay Log tab for some reason. I'm not well versed in any form of coding so help is greatly appreciated!

function submit() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Food Sales");
  var datasheet = ss.getSheetByName("Pay Log");
  var datasheet = ss.getSheetByName("Sales Log");
  
  var values = [[formSS.getRange("E10").getValue(),
                 formSS.getRange("E11").getValue(),
                 formSS.getRange("E12").getValue()]];
                
  
  datasheet.getRange(datasheet.getLastRow() 1, 1, 1, 3).setValues(values);
 
}

CodePudding user response：

Using getValues

function submit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Food Sales");
  var datasheet1 = ss.getSheetByName("Pay Log");
  var datasheet2 = ss.getSheetByName("Sales Log");
  var values = formSS.getRange(10, 5, 3).getValues();//getValues()
  datasheet1.getRange(datasheet1.getLastRow()   1, 1, 1, 3).setValues(values);
  datasheet2.getRange(datasheet2.getLastRow()   1, 1, 1, 3).setValues(values);
}

CodePudding user response：

This is because you are using the same variable dataSheet for two different sheets. On the line var datasheet = ss.getSheetByName("Pay Log"); you assigned the Pay Log sheet on the datasheet but then replaced it by the Sales Log sheet on the next line var datasheet = ss.getSheetByName("Sales Log");

What you can do is create and assign different variables for both sheets before pasting. Simple way you can do it is by the code below.

function submit() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Food Sales");
  var datasheet1 = ss.getSheetByName("Pay Log");
  var datasheet2 = ss.getSheetByName("Sales Log");
  

  var values = [[formSS.getRange("E10").getValue(),
             formSS.getRange("E11").getValue(),
             formSS.getRange("E12").getValue()]];
            

 datasheet1.getRange(datasheet1.getLastRow() 1, 1, 1, 3).setValues(values);
 datasheet2.getRange(datasheet2.getLastRow() 1, 1, 1, 3).setValues(values);

}

Or in case you have more sheets to paste your data into, you can use the getSheets() function which will return an array that contains all your sheets. You can then loop through the sheets starting from your second sheet. Example code below

function submit() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Food Sales");
  var dataSheet = ss.getSheets();

  var values = [[formSS.getRange("E10").getValue(),
             formSS.getRange("E11").getValue(),
             formSS.getRange("E12").getValue()]];

  for (i = 1; i < dataSheet.length; i  ){
    dataSheet[i].getRange(dataSheet[i].getLastRow() 1, 1, 1, 3).setValues(values);
    };

 };

Refer to this link for more details on the Class Spreadsheet: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getsheets