Remote Storage for AdWords Scripts

remoteStorage_adWordsScripts.js

/**
 * @author Thomas Steiner (tomac@google.com)
 * @license CC0 1.0 Universal (CC0 1.0)
 *
 * Provides a simple key-value storage API modeled closely after
 * the localStorage API in Web browsers, but tailored to AdWords Scripts.
 * AdWords Scripts, due to execution time limits published at
 * https://developers.google.com/adwords/scripts/docs/limits,
 * forces users to store the state of a given script using either labels
 * (https://developers.google.com/adwords/scripts/docs/tips#labels), or
 * some other mechanism. This script provides such mechanism.
 *
 * Usage:
 *
 * 1) Create a Spreadsheet and pass its URL to the constant SPREADSHEET_URL.
 *
 * 2) Copy and paste the script into your AdWords script. This exposes
 * a remoteStorage object with the following API:
 *
 * - remoteStorage.setItem('myKey', {value: 'my_value'});
 * - remoteStorage.getItem('myKey'); // returns {value: 'my_value'}
 * - remoteStorage.removeItem('myKey'); // removes the item with key 'myKey'
 * - remoteStorage.getLength(); // returns 0
 * - remoteStorage.clear();
 *
 * Note: unlike with localStorage, you do not need to JSON.parse/stringify
 * the values, the script takes care of this.
 */
var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/foobar';

var remoteStorage = (function() {
  'use strict';
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = spreadsheet.getSheetByName('_remoteStorage') !== null ?
      spreadsheet.getSheetByName('_remoteStorage') :
      spreadsheet.insertSheet('_remoteStorage');
  var length = sheet.getDataRange().getValues().length;
  return {
    getItem: function(key) {
      if (!key) {
        return;
      }
      key = key.toString();
      var values = sheet.getDataRange().getValues();
      for (var i = 0, lenI = values.length; i < lenI; i++) {
        var currentKey = values[i][0].toString();
        if (currentKey === key && values[i][1]) {
          return JSON.parse(values[i][1]);
        }
      }
      return null;
    },
    setItem: function(key, value) {
      if (!key || !value) {
        return;
      }
      key = key.toString();
      value = JSON.stringify(value);
      var values = sheet.getDataRange().getValues();
      for (var i = 0, lenI = values.length; i < lenI; i++) {
        var currentKey = values[i][0].toString();
        if (currentKey === key) {
          var range =  sheet.getRange(i + 1, 1, 1, 2);
          length++;
          return range.setValues([[key, value]]);
        }
      }
      length++;
      return sheet.appendRow([key, value]);
    },
    removeItem: function(key) {
      if (!key) {
        return;
      }
      key = key.toString();
      var values = sheet.getDataRange().getValues();
      for (var i = 0, lenI = values.length; i < lenI; i++) {
        var currentKey = values[i][0].toString();
        if (currentKey === key) {
          length--;
          return sheet.deleteRow(i + 1);
        }
      }
    },
    key: function(index) {
      var values = sheet.getDataRange().getValues();
      if (values[index][0]) {
        return values[index][0].toString();
      }
      return null;
    },
    clear: function() {
      sheet.clear();
      length = 0;
    },
    getLength: function() {
      return length;
    }
  };
})();