Search code examples
javascriptnode.jsgoogle-apps-scriptgoogle-sheets-api

How to retrieve a spreadsheet apps script id using google api

I'm looking for a way programmatically using NodeJS and google api to associate an existing apps script file(id) with a newly created spreadsheet/s or else to retrieve the associated script id from a newly created spreadsheet so I can inject a custom script code

so far I tried with google sheet api to retrieve the associated scrip id without any success, and I didn't find any documentation on how to associate a spreadsheet with an existing script id.

Solution

  • About your following question,

    I'm looking for a way programmatically using NodeJS and google api to associate an existing apps script file(id) with a newly created spreadsheet/s or else to retrieve the associated script id from a newly created spreadsheet so I can inject a custom script code

    if my understanding is correct, how about the following answer?

    Prerequisite:

    First, the prerequisite is as follows.

    • You have a Google Apps Script project.
    • You know the script ID (file ID) of Google Apps Script.
    • You create a new Google Spreadsheet and want to add the above existing Google Apps Script to the created Spreadsheet as the container-bound script.

    Sample flow:

    In the current stage, the existing Google Apps Script project cannot be directly linked as the container-bound script while keeping the script ID (file ID) of Google Apps Script. However, I thought that your goal could be achieved by creating a new Google Apps Script project to the existing Google Spreadsheet. In order to achieve this, how about the following flow?

    1. Confirm the script ID (file ID) of your current Google Apps Script.
      • This script is put into the new Google Spreadsheet as the container-bound script.
    2. Create a new Google Spreadsheet. And, please confirm the Spreadsheet ID of the created Spreadsheet.
    3. Retrieve an object from your current Google Apps Script using the script ID with "Method: projects.getContent".
    4. Create a new container-bound script to the new Google Spreadsheet using the Spreadsheet ID with "Method: projects.create". And, retrieve the script ID of the created container-bound script from the response value of the API.
    5. Put the script retrieved from your current Google Apps Script into the created container-bound script using the script ID of the created container-bound script with "Method: projects.updateContent".

    By this flow, the new Google Spreadsheet including a container-bound script of your current Google Apps Script is created. I guessed that this might be your expected result.

    Note:

    • In this answer, it supposes that you have already been able to get and put values using Google Apps Script API.

    References: