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?

CodePudding user response：

• 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 I am a editor with malicious intentions, I'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 I 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 script 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.

• 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. Criticisms are welcome.