Securely Storing API Secrets used in Google Apps Script - Published Library

Question

In Google Sheets, there is a script that uses "UrlFetchApp" to obtain information from an external API that requires an API key to be included in each call.

The sheet has a number of editors but only the owner should be able to see the API key, so storing the key in the script itself or using the PropertiesService is not an option.

Would the following solution keep sheet editors from seeing the key?

1. Create a new stand-alone Apps Script project.

2. In the stand-alone script, create the following function:

    function fetchData(idFromSheetScript) {
      var secret = '/abc123';
      var id = idFromSheetScript;
      var uri = 'https://.../'; 
      var url = uri+id+secret;
      var data = UrlFetchApp.fetch(url);
      return data;
    }
   

3. Deploy the stand-alone script as a Library. Do not share the project with anyone.

4. In the Google Sheets-bound script, import the Library and use fetchData() function from the Library.

    var response = fetchData('10');
   

Would the editors of the sheet where the Library is imported be able to see or obtain (through logging or otherwise) the "secret" variable in the Library or would they only be able to see the function's returned variable?

Answer 1

• There are problems with your approach:

  • The library needs to be shared with atleast "view level" access to the end user, otherwise it won't function. So, point 3 is infeasible.

  • The library source code of library with identifier MyLibrary can also be retrieved with

    console.log(MyLibrary.fetchData.toString());
    //where fetchData is one of the function names in MyLibrary
    

• Installable edit trigger:

  • These triggers run under the authority of the user who created the trigger. So, libraries can be shared with one user/dummy Google account, and have that user install a trigger, while limiting library source code access to every other editor.

  • However, If there's a editor with malicious intentions, they'd simply edit the onEdit() function to:

    function onEditInstalled(){
      SpreadsheetApp.getActiveRange().setValue(MyLibrary.fetchData.toString())
    }
    

    and edit something to get the source+api key. But they can do more nefarious things to the user who created the trigger, if installable trigger had more permissions(like access to Gmail or Drive) and if set to always run at the latest deployment.

  • The above maybe avoided by setting the trigger to always run at a predetermined version and not at HEAD/latest version. This can be changed when setting up triggers in apps script dashboard user interface. To create a version, you can create a dummy library/webapp deployment or use the api. This protects the code against any modifications, because a version is like a 'snapshot' of current code. Once a trigger is set to execute at a certain version, it cannot be changed by other editors. There is also currently no way to modify a existing version by the owner or a editor. Versions are immutable.

• Since script properties are not shared between the library and the including script, you may use that to hide the api key.

• Another option is to deploy the standalone script as a web app with access: anyone including anonymous, but use your authentication mechanism using identity tokens( ScriptApp.getIdentityToken()). But you need to build a proper validation mechanism on the web app side for the id token.

  Note that all these workarounds are not pen tested for security, but provided as a concept based on experience. Security is relative. Criticisms are welcome.