2

I have scenario where I have more than 60 sheet, and all have appscript project behind them running and managing those sheet. I want to updat/control those appscript projects all at once, from one different project. So that I don't have to manually go and update them.

I know this solution where we can update it by manually getting OAuth Token from single appscript project but, Is there any way we can just mention sheetID and it updates project behind all those sheet all at once?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
vector
  • 1,032
  • 1
  • 4
  • 16

3 Answers3

2

From the question

Is there any way we can just mention sheetID and it updates project behind all those sheet all at once?

There is no direct way to do this. Related issue Retrieving Project ID of Container-Bound script (created in 2018, as of the end of 2021 it still has the "New" status).

Assuming that by "SheetID" you mean the spreadsheet id, it's possible to use the Spreadsheet Service and the Drive Service to get a Class Spreadsheet or Class File object, respectively. Unfortunately those classes hasn't any method to retrieve the bounded Google Apps Script project.

The same for the Sheets API, Drive API and the Apps Script API.

The indirect way is to build a list of spreadsheets and their respective bounded scripts but it's clear that this is not what you are looking for. Anyway, build the list of spreadsheet/script projects pairs and store it somewhere (it might be an spreadsheet if the list is not humongous. CLAPS has a command to retrieve the list of the recent projects, and it's possible to get the container id from the script project metadata (see https://developers.google.com/apps-script/api/reference/rest/v1/projects/get)

Considering the above, the advice to make it easier to maintain your scripts "for the next time" is to either use a library or to create and add-on.

Resources

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thanks that Clasp workaround looks something I can try, one more thing i want to ask, I have many sheets which actually use library but while adding those library in the sheet I set it on static versions, now when I update main project, and republish it, new changes doesn't works on those sheets where library are currently in use until i set it again on new version, I know I am doing something wrong here! – vector Dec 30 '21 at 05:04
  • @vector I think that, on the spreadsheets that are using the library, you should should switch the project to use the legacy editor in order to be able to use de "Development Mode". See https://developers.google.com/apps-script/guides/libraries for details and if you need further help please post a new question. – Rubén Dec 30 '21 at 07:13
0

If you're asking can you reference different codes from the same document but not the same sheet in a new code, the answer is yes. But, there is no way to manipulate this code other than editing the original code. I would recommend manually getting an OAuth Token from a single Apps Script Rroject.

taylor.2317
  • 582
  • 1
  • 9
  • 23
  • Thanks for your response. I can use that solution but I don't have script ID of those sheets, and i don't want to go manually look for it, and each day numbers of sheets are increasing. Just wanted to get a way around where I can manage script behind those sheets from one single project, to ease my work. – vector Dec 29 '21 at 04:31
  • Hi - I don't think that is possible, so my answer is the only solution. – taylor.2317 Dec 29 '21 at 22:29
0

A different way to control those Spreadsheets can be with a NodeJS project locally, using Clasp and Multi-clasp2 (https://www.npmjs.com/package/multi-clasp2).

Fabrizio Antonangeli
  • 11
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 14 '22 at 06:25