Storing API keys and secrets in Google AppScript user property

Question

I'm quite new to Google AppScript, trying to code a connector to a custom REST API. For that API I need an API key (or secret), that is per user. Since storing a secret in plain text inside the script is not the best idea, I was thinking to store it in the Google PropertyService and retrieve it from there. Like this:

var userProperties = PropertiesService.getUserProperties();
var apiKey = userProperties.getProperty('MY_SECRET')

But what I don't understand is, how can the user ever store the key first? I haven't found any place where the user (in this case me) can view or edit properties. Then I found this nice introduction to user properties that creates a menu in the script container allowing the user to enter the secrets manually.

const API_KEY = 'API_KEY';

var ui = SpreadsheetApp.getUi();
var userProperties = PropertiesService.getUserProperties();


function onOpen(){
  ui.createMenu('API Keys')
    .addItem('Set API Key', 'userPromptApiKey')
    .addItem('Delete API Key', 'deleteApiKey')
  .addToUi();
}


function userPromptApiKey(){
  var userValue = ui.prompt('API Key ', ui.ButtonSet.OK);
  // ToDo: add current key to the prompt
  userProperties.setProperty(API_KEY, userValue.getResponseText());
}


function deleteApiKey(){
  userProperties.deleteProperty(API_KEY)
}

The problem is, that my script is not bound to any container (no spreadsheet, no document). Instead I want to use it in Google DataStudio later on. This is why

SpreadsheetApp.getUi();

does not work. Any idea or recommendation on how to deal with that? Is there any other recommended way to work with the secrets?

Answer 1

Now, some weeks later I've learned a lot. First, you need to differentiate between UI and the logical script. Second, whether it is a container-bound or stand-alone script.

A container-bound script is bound to Google Spreadsheet, Google Doc or any other UI that allows user interaction. In such case, you can access the UI in the code and add custom menus to the UI that will invoke methods in your script once the user clicks on that menu. The disadvantage is that you need to know if it is a Spreadsheet or Doc since the UI class differs. You also need to instruct the user to enter his or her credentials using the custom menu. There is a very nice instruction online. The following code snipped is inspired by the instruction. Make sure to create a trigger for onOpen.

var ui = SpreadsheetApp.getUi();
var userProperties = PropertiesService.getUserProperties();

const API_KEY = 'api.key';

function onOpen(){
  ui.createMenu('Credentials & Authentication')
    .addItem('Set API key', 'setKey')
    .addItem('Delete API key', 'resetKey')
    .addItem('Delete all credentials', 'deleteAll')
  .addToUi();
}

function setKey(){
  var scriptValue = ui.prompt('Please provide your API key.' , ui.ButtonSet.OK);
  userProperties.setProperty(API_KEY, scriptValue.getResponseText());
}

function resetKey(){
  userProperties.deleteProperty(API_KEY);
}

function deleteAll(){
  userProperties.deleteAllProperties();
}

For a standalone script you need to find any other way to connect to the UI. In my situation I was implementing a custom connector for Google Data Studio for which there is a very nice example online as well. There is a quite detailed instruction on authentication and an API reference on authentication as well. This custom connector for Kaggle was very helpful as well. It is open-source on the Google Data Studio GitHub. The following demo code is inspired by those examples. Have a look at getCredentials, validateCredentials, getAuthType, resetAuth, isAuthValid and setCredentials.

var cc = DataStudioApp.createCommunityConnector();

const URL_DATA = 'https://www.myverysecretdomain.com/api';
const URL_PING = 'https://www.myverysecretdomain.com/ping';
const AUTH_USER = 'auth.user'
const AUTH_KEY = 'auth.key';
const JSON_TAG = 'user';

String.prototype.format = function() {
  // https://coderwall.com/p/flonoa/simple-string-format-in-javascript
  a = this;
  for (k in arguments) {
    a = a.replace("{" + k + "}", arguments[k])
  }
  return a
}

function httpGet(user, token, url, params) {
  try {
    // this depends on the URL you are connecting to
    var headers = {
      'ApiUser': user,
      'ApiToken': token,
      'User-Agent': 'my super freaky Google Data Studio connector'
    };

    var options = {
      headers: headers
    };

    if (params && Object.keys(params).length > 0) {
      var params_ = [];
      for (const [key, value] of Object.entries(params)) {
        var value_ = value;
        if (Array.isArray(value))
          value_ = value.join(',');

        params_.push('{0}={1}'.format(key, encodeURIComponent(value_)))
      }

      var query = params_.join('&');
      url = '{0}?{1}'.format(url, query);
    }

    var response = UrlFetchApp.fetch(url, options);

    return {
      code: response.getResponseCode(),
      json: JSON.parse(response.getContentText())
    }  
  } catch (e) {
    throwConnectorError(e);
  }
}

function getCredentials() {
  var userProperties = PropertiesService.getUserProperties();
  return {
    username: userProperties.getProperty(AUTH_USER),
    token: userProperties.getProperty(AUTH_KEY)
  }
}

function validateCredentials(user, token) {
  if (!user || !token) 
    return false;

  var response = httpGet(user, token, URL_PING);

  if (response.code == 200)
    console.log('API key for the user %s successfully validated', user);
  else
    console.error('API key for the user %s is invalid. Code: %s', user, response.code);

  return response;
}  

function getAuthType() {
  var cc = DataStudioApp.createCommunityConnector();
  return cc.newAuthTypeResponse()
    .setAuthType(cc.AuthType.USER_TOKEN)
    .setHelpUrl('https://www.myverysecretdomain.com/index.html#authentication')
    .build();
}

function resetAuth() {
  var userProperties = PropertiesService.getUserProperties();
  userProperties.deleteProperty(AUTH_USER);
  userProperties.deleteProperty(AUTH_KEY);

  console.info('Credentials have been reset.');
}

function isAuthValid() {
  var credentials = getCredentials()
  if (credentials == null) {
    console.info('No credentials found.');
    return false;
  }

  var response = validateCredentials(credentials.username, credentials.token);
  return (response != null && response.code == 200);
}

function setCredentials(request) {
  var credentials = request.userToken;
  var response = validateCredentials(credentials.username, credentials.token);

  if (response == null || response.code != 200) return { errorCode: 'INVALID_CREDENTIALS' };

  var userProperties = PropertiesService.getUserProperties();
  userProperties.setProperty(AUTH_USER, credentials.username);
  userProperties.setProperty(AUTH_KEY, credentials.token);

  console.info('Credentials have been stored');

  return {
    errorCode: 'NONE'
  };
}

function throwConnectorError(text) {
  DataStudioApp.createCommunityConnector()
    .newUserError()
    .setDebugText(text)
    .setText(text)
    .throwException();
}

function getConfig(request) {
  // ToDo: handle request.languageCode for different languages being displayed
  console.log(request)

  var params = request.configParams;
  var config = cc.getConfig();

  // ToDo: add your config if necessary

  config.setDateRangeRequired(true);
  return config.build();
}

function getDimensions() {
  var types = cc.FieldType;

  return [
    {
      id:'id',
      name:'ID',
      type:types.NUMBER
    },
    {
      id:'name',
      name:'Name',
      isDefault:true,
      type:types.TEXT
    },
    {
      id:'email',
      name:'Email',
      type:types.TEXT
    }
  ];
}

function getMetrics() {
  return [];
}

function getFields(request) {
  Logger.log(request)

  var fields = cc.getFields();

  var dimensions = this.getDimensions();
  var metrics = this.getMetrics();
  dimensions.forEach(dimension => fields.newDimension().setId(dimension.id).setName(dimension.name).setType(dimension.type));  
  metrics.forEach(metric => fields.newMetric().setId(metric.id).setName(metric.name).setType(metric.type).setAggregation(metric.aggregations));

  var defaultDimension = dimensions.find(field => field.hasOwnProperty('isDefault') && field.isDefault == true);
  var defaultMetric = metrics.find(field => field.hasOwnProperty('isDefault') && field.isDefault == true);

  if (defaultDimension)
    fields.setDefaultDimension(defaultDimension.id);
  if (defaultMetric)
    fields.setDefaultMetric(defaultMetric.id);

  return fields;
}

function getSchema(request) {
  var fields = getFields(request).build();
  return { schema: fields };
}

function convertValue(value, id) {  
  // ToDo: add special conversion if necessary
  switch(id) {      
    default:
      // value will be converted automatically
      return value[id];
  }
}

function entriesToDicts(schema, data, converter, tag) {

  return data.map(function(element) {

    var entry = element[tag];
    var row = {};    
    schema.forEach(function(field) {

      // field has same name in connector and original data source
      var id = field.id;
      var value = converter(entry, id);

      // use UI field ID
      row[field.id] = value;
    });

    return row;
  });
}

function dictsToRows(requestedFields, rows) {
  return rows.reduce((result, row) => ([...result, {'values': requestedFields.reduce((values, field) => ([...values, row[field]]), [])}]), []);
}

function getParams (request) { 
  var schema = this.getSchema();
  var params;

  if (request) {
    params = {};

    // ToDo: handle pagination={startRow=1.0, rowCount=100.0}
  } else {
    // preview only
    params = {
      limit: 20
    }
  }

  return params;
}

function getData(request) {
  Logger.log(request)

  var credentials = getCredentials()
  var schema = getSchema();
  var params = getParams(request);

  var requestedFields;  // fields structured as I want them (see above)
  var requestedSchema;  // fields structured as Google expects them
  if (request) {
    // make sure the ordering of the requested fields is kept correct in the resulting data
    requestedFields = request.fields.filter(field => !field.forFilterOnly).map(field => field.name);
    requestedSchema = getFields(request).forIds(requestedFields);
  } else {
    // use all fields from schema
    requestedFields = schema.map(field => field.id);
    requestedSchema = api.getFields(request);
  }

  var filterPresent = request && request.dimensionsFilters;
  //var filter = ...
  if (filterPresent) {
    // ToDo: apply request filters on API level (before the API call) to minimize data retrieval from API (number of rows) and increase speed
    // see https://developers.google.com/datastudio/connector/filters

    // filter = ...   // initialize filter
    // filter.preFilter(params);  // low-level API filtering if possible
  }

  // get HTTP response; e.g. check for HTTT RETURN CODE on response.code if necessary
  var response = httpGet(credentials.username, credentials.token, URL_DATA, params);  

  // get JSON data from HTTP response
  var data = response.json;

  // convert the full dataset including all fields (the full schema). non-requested fields will be filtered later on  
  var rows = entriesToDicts(schema, data, convertValue, JSON_TAG);

  // match rows against filter (high-level filtering)
  //if (filter)
  //  rows = rows.filter(row => filter.match(row) == true);

  // remove non-requested fields
  var result = dictsToRows(requestedFields, rows);

  console.log('{0} rows received'.format(result.length));
  //console.log(result);

  return {
    schema: requestedSchema.build(),
    rows: result,
    filtersApplied: filter ? true : false
  };
}

If none of this fits your requirements, then go with a WebApp as suggested in the other answer by @kessy.

Answer 2

You need a UI to get input data from the user.

You can create a Web App to build an interface to get the Keys.

Also, if you are building the script and not publishing it yet, you can hardcode the keys until the release.