Saving an array globally

Question

What I'm trying to achieve

I have a spreadsheet with 2 sheets A & B.

• A has 2 columns - Name, Amount (Master List)

• B has 4 columns - Name, Amount, X, Y (Transaction List)

Name column of Sheet B references Name column of Sheet A for data. Whenever a name is selected, I want to populate Amount column in B with Amount in column of sheet A as a placeholder which users can override. For this, I plan to load the Sheet A data in an array (available Globally) so that in onEdit(e) I can refer that array instead of accessing Sheet B.

But the options I could find - CacheService and PropertyService - save only string values. But I want to have:

var myGlobalArray = [];

function on init(){
    //iterate and fill the array such that it has following output
    //myGlobalArray[Name1] = 1
    //myGlobalArray[Name2] = 2
    //myGlobalArray[Name3] = 3
}

function onEdit(e){ 
  //if selected value is name1, populate myGolbalArray[Name1] value in Amount
}

Question Where & how to define myGlobalArray?

I tried to use cache service with JSON.Stringify and JSON.parse but the array is empty in onEdit.

Answer 1

Each call to your script creates a new instance of your script with its own unique globals. Every time you call a script you will actually find a global "this" for that specific instance. You are correct to look at PropertyService as a persistent way to save data.

Right off I See that your globalArray is not set up right:

var myGlobalArray = [];

needs to be

 var myGlobalArray = {};
 myGlobalArray['name1'] = 1
 myGlobalArray['name2'] = 2
 myGlobalArray['name3'] = 3

 //myGlobalArray = {name3=3.0, name1=1.0, name2=2.0}
 var stringArray =  JSON.stringify(myGlobalArray)
 //{"name1":1,"name2":2,"name3":3};

Now that can be saved to and read from the property store.

PropertiesService.getScriptProperties().setProperty("NameArray", stringArray);
stringArray = PropertiesService.getScriptProperties().getProperty("NameArray");
myGlobalArray = JSON.parse(stringArray);
Logger.log(myGlobalArray['name1']) // returns 1

Answer 2

It's true that CacheService and PropertyService save only string values, but you can store any scalar data by using the JSON utilities JSON.stringify() and JSON.parse().

// Save an array in cache service
CacheService.getPublicCache()
            .put("myGlobalArray", JSON.stringify(myGlobalArray));

// Retrieve an array from property service
var myGlobalArray = JSON.parse( CacheService.getPublicCache()
                                                 .get("myGlobalArray") );

// Save an array in property service
PropertiesService.getDocumentProperties()
                 .setProperty("myGlobalArray", JSON.stringify(myGlobalArray));

// Retrieve an array from property service
var myGlobalArray = JSON.parse( PropertiesService.getDocumentProperties()
                                                 .getProperty("myGlobalArray") );

When a variable is called "Global", we are referring to its scope, saying that it is available to all code within the same module. (You can read more about scope in What is the scope of variables in JavaScript?)

But since you're looking at CacheService and PropertyService, you already know that scope is only part of the problem. Each time that onEdit() is invoked, it will be running in a new execution instance on one of Google's servers. A value that had been in a global variable in a previous instance will not be available to this new instance. Therefore, we need to populate our "global variable" in each new invocation of our script.

An elegant way to reference global variables is as names properties of the special this object. For example, every function in our script can refer to this.myGlobalArray.¹

You can adapt the getRssFeed() example from the Class Cache documentation into get_myGlobalArray(), say. Then your onEdit() trigger needs only to call that first to make sure that this.myGlobalArray contains the relevant array data.

function onEdit(e){ 
  get_myGlobalArray();

  //if selected value is name1, populate myGlobalArray[Name1] value in Amount
  ...
  sheet.getRange(e.range.getRow(),2).setValue(myGlobalArray[e.value]);
}

/**
 * Ensure the global variable "myGlobalArray" is defined and contains the
 * values of column A in SheetA as an array.
 */
function get_myGlobalArray() {
  if (typeof this.myGlobalArray == 'undefined') {
    // Global variable doesn't exist, so need to populate it
    // First, check for cached value
    var cache = CacheService.getPublicCache();
    var cached = cache.get("myGlobalArray");
    if (cached) {
      // We have a cached value, so parse it and store in global
      this.myGlobalArray = JSON.parse(cached);
    }
    else {
      // No value in the cache, so load it from spreadsheet
      var data = SpreadsheetApp.getActive().getSheetByName("Sheet A").getDataRange().getValues();
      this.myGlobalArray = {};
      for (var row=0; row<data.length; row++) {
        this.myGlobalArray[data[row][0]] = data[row][6];
      }

      // Stringify and store the global into the cache
      cache.put("myGlobalArray", JSON.stringify(this.myGlobalArray));
    }
  }
}

Edit: Associative Array

In the comment within onEdit(), it's indicated:

//if selected value is name1, populate myGolbalArray[Name1] value in Amount

This implies that myGlobalArray is an associative array, where the index is a non-integer value. This requirement is now reflected in the way that this.myGlobalArray gets populated when read from the spreadsheet.

for (var row=0; row<data.length; row++) {
  this.myGlobalArray[data[row][0]] = data[row][6];
  //                 ^^^^^^^^^^^^    ^^^^^^^^^^^^
  //  Name ---------------/           /
  //  Amount ------------------------/
}

Much has been written about the different flavours of Javascript arrays, for instance Javascript Associative Arrays Demystified.

---

¹ Actually, only functions with global scope would understand this to mean "global to the script". Functions that are contained inside objects would interpret this to mean their host object only. But that's a story for another day.