0

I am trying to create a trigger to get id when there is a change in sheet selection which is working fine when each google sheet has its own google app script, but I want to create a google app script which will work for every sheet based on spreadsheet-id.

So I tried running a function that has SpreadSheet.openById("SpreadSheet_id) from the Google Apps Script Editor, but it assumes the active sheet to be Sheet0 by default, even though the sheet that is open in my Google Sheets is Sheet1, and therefore returns incorrect things.

Is there any some other method, or solution to above problem I am facing?

This is the code I am using.

function getActiveSheetByURL(id) {
  var ss = SpreadsheetApp.openById(id).getActiveSheet();
  console.log(ss.getSheetId());
}
Improve this question
3
  • The question doesn't include enough details to let others know what are you doing. Google Apps Script doesn't include SpreadSheet as a reserved name, so you should explain what it is. It's a common practice to include sample code, usually referred to as minimal complete example or minimal complete and verifiable example (mcve) as text, the textual error message, / logs.
    – Rubén ♦
    Oct 21 at 17:00
  • You might save a lot of time if you study developers.google.com/apps-script/guides/sheets. Please note that this briefly mentions some basic tasks like reading and writing values from and to a spreadsheet. I also mention very relevant concepts and points to other documents. Please note that besides this guide there are more resources, including quickstarts that are examples used to show how to apply the concepts and to do typical tasks.
    – Rubén ♦
    Oct 21 at 17:01
  • Going back to this specific question, the question should include enough details to understand why you are using openById(id) instead of getActiveSpreadsheet() or why you are not using getActiveSheet() if what you are looking is to get the the active sheet.
    – Rubén ♦
    Oct 21 at 17:12

1 Answer 1

Reset to default
0

Because you are calling the function from a spreadsheet trigger, instead of using SpreadsheetApp.openById(id) to get the active sheet, you should use the event object.

To learn about the event objects in Google Apps Script, go to https://developers.google.com/apps-script/guides/triggers/events

All the Spreadsheet event objects have a source property that returns the Class Spreadsheet object corresponding to the active spreadsheet. The Edit event object also includes the range property corresponding to the edited range.

Using the on-edit installable trigger, use the range property with the getSheet() method.

function respondToEdit(e){
   const sheet = e?.range.getSheet();
   console.log(sheet?.getName() ?? 'Remember, this should be called by a on edit trigger');
}

NOTES

null will be assigned to e when the function is run by clicking the Run button. Because of this, I use ?. , ?? and a custom message to handle accidental executions.

You might try to use source instead of range, but in such case, use getActiveSheet(). Theoretically, this should work too. I feel more confident about using the edit method because it is only triggered by edit events, while other events might start the change method and require more complex handling.

SpreadsheetApp.openById(id) is unaware of the trigger / execution context other than the authorization to be executed; it gets an independent spreadsheet instance without the user selection information to return the first cell as the active range. Hence, the first sheet is returned as the active sheet, no matter how this method was called.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.