0

I am working on a simple game in google sheets, where a user selects some options and presses a button, then the selections are recorded on a log. I have it working perfectly but only if the log is on a different sheet within the same "workbook". I would really like to have the log be a separate file, but I can not figure out how to get the script to call up this other sheet.

I have tried every code I have found online doing various searches but still can not figure out how to get it to work. As soon as I try to change the destination to an outside sheet I get errors.

Here is what works:

// Function to submit the tempt fate gameplay data to Action Log sheet
function submitDatatemptfate() {
     
  var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var ActionList= myGoogleSheet.getSheetByName("Action List"); //delcare a variable and set with the User Form worksheet

  var ActionLog = myGoogleSheet.getSheetByName("Action Log"); ////delcare a variable and set with the Database worksheet

  //to create the instance of the user-interface environment to use the messagebox features
  var ui = SpreadsheetApp.getUi();
  
  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
  // close the dialog by clicking the close button in its title bar.
  var response = ui.alert("Execute", 'Do you want to execute this action?',ui.ButtonSet.YES_NO);

  // Checking the user response and proceed with clearing the form if user selects Yes
  if (response == ui.Button.NO) 
  {return;//exit from this function
  }

Here, Action List is the user interface and Action Log is where it is recorded. I want to use this sheet to record the log instead: https://docs.google.com/spreadsheets/d/1fTE_TnuysR5LKbyPDzPxU63hOu6BISH6YYJ0udoViZA/edit#gid=1154300080

I am relatively new to apps script so I feel like the solutions I tried perhaps I am just entering the code incorrectly or don't know which things to change and which not to in order to customize for my own use. For example, here is the latest solution I tried:

    // Function to submit the tempt fate gameplay data to Log sheet
function submitDatatemptfate() {
     
  var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var Game= myGoogleSheet.getSheetByName("Game"); //delcare a variable and set with the Game worksheet

  var Log = SpreadsheetApp.openById("1154300080"); ////delcare a variable and set with the Log worksheet

  //to create the instance of the user-interface environment to use the messagebox features
  var ui = SpreadsheetApp.getUi();
  
  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also
  // close the dialog by clicking the close button in its title bar.
  var response = ui.alert("Last chance...", 'Are you sure you want to Tempt Fate?',ui.ButtonSet.YES_NO);

  // Checking the user response and proceed with clearing the form if user selects Yes
  if (response == ui.Button.NO) 
  {return;//exit from this function
  }

...and I get this error:

Exception: Unexpected error while getting the method or property openById on object SpreadsheetApp.

Thank you in advance for any help or advice!

A.Bully
  • 3
  • 1

1 Answers1

0

I think you are using wrong id value in the below statement. It should be a spreadsheet id whereas you seem to be using sheet (tab) id there.

 var Log = SpreadsheetApp.openById("1154300080");

For eg In the below spreadsheet URL, the id is "151JYFMfa9p7NvsJ9P8o9az2-L6iGtTSkG-ZSz6oaOzs" which needs to be used in openById.

https://docs.google.com/spreadsheets/d/151JYFMfa9p7NvsJ9P8o9az2-L6iGtTSkG-ZSz6oaOzs/edit

ab.it.gcp
  • 151
  • 1
  • 14
  • Ok so I tried replacing the id and got this error: Exception: The parameters (String,number) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange. Then googled and tried the solution to that error found here: https://stackoverflow.com/questions/57633283/sheet-getrange-throwing-signature-mismatch-exception-when-not-using-a1-notation And now I am getting this error: TypeError: myGoogleSheet.getSheetByName is not a function This is pretty much the stuff I have been going through the past couple days trying to troubleshoot. – A.Bully Mar 09 '23 at 21:12
  • Check this link on how to copy a sheet to another spreadsheet. https://stackoverflow.com/questions/63830560/google-sheet-script-copy-sheet-to-another-spreadsheet-and-name-new-sheet-in-ta – ab.it.gcp Mar 10 '23 at 04:26