1

I'm using Google Apps Script to make many copies of spreadsheets that have embedded code inside of them. I want to be able to make a log of the Script IDs of each copy of the spreadsheet, without having to manually go into each spreadsheet, then script file, and copy the script ID. I know that within a script, I can use ScriptApp.getScriptId(), but of course that won't work from a standalone script only referencing the copy of the file.

tl;dr: if I have a script embedded in a Google Sheet, and I have the File ID for the Google Sheet, can I programmatically access the Script ID using Apps Script?

Lle.4
  • 516
  • 4
  • 17
  • 4
    I thought that your goal might be related to this issue tracker. https://issuetracker.google.com/issues/111149037 For example, how about adding a star? – Tanaike Feb 14 '22 at 01:50
  • Curious to know, what do you do with these Script ID's. Maybe what you really want is move your code to a library and then every spreadsheet would use the same code. – TheWizEd Feb 14 '22 at 15:01
  • Thanks for the suggestion and the feedback! I'm actually hoping to be able to programmatically modify the code within the script files. They reference a library, and I want to be able to programmatically update the library versions in the manifest json file. Because of that, I need to be able to directly access the script files via file ID. – Lle.4 Feb 14 '22 at 18:22
  • Thank you for replying. In your situation, I thought that this thread might be useful. https://stackoverflow.com/q/60674161/7108653 – Tanaike Feb 15 '22 at 02:41

1 Answers1

0

Maybe you can do the following, have a try:

Step 1: You need to insert in every script the following function at the top:

function MyScriptID(){return ScriptApp.getScriptId()}

Step 2: using a separate spreadsheet to manipulate data (a kind of master sheet), you will run the following function:

function FindScriptID() {


// Insert the ID of the sheet you want to retrieve the script Id from
    var sheet = SpreadsheetApp.openByUrl('your ID')

    // name the sheet where you want to insert the formula

    var destination = sheet.getSheetByName("mysheet");

    // Choose a cell to insert the formula - example: (4,2)

    destination.getRange(4, 2).setFormula('=MyScriptID()');

    SpreadsheetApp.flush();

    // get the displayed ID from the formula, which is actually the ID of the attached script

    var myscriptID = destination.getRange(4, 2).getDisplayValue();

    Logger.log(myscriptID)


// You can now write the ID of your script within the sheet you've ran the above function, choose a sheet where you wan to write the ID - e.g. Sheet1

   var currentsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

//Choose a cell to write the link of you your script or just the ID

currentsheet.getRange(2, 2).setValue('https://script.google.com/a/ais.sch.sa/d/' + myscriptID + '/edit')


}

You can now improve this master sheet if you have many spreadsheets to work with in order to iterate though all of them in one go:

Within the master sheet:

  • You list all your spreadsheets (urls)
  • you include in all their scripts (function MyScriptID(){return ScriptApp.getScriptId()}) at the top as mentioned before
  • you iterate through all the spreadsheet links to insert the formula
  • You read the displayed values
  • You write into the master sheet the retrieved scripts IDs or links

You'll get sometimes some errors with the displayed values, like ('Loading...')

Nabnub
  • 953
  • 6
  • 15