javascript - Saving an array globally - Stack Overflow

admin管理员组

文章数量:1399145

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.

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.

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Jan 14, 2016 at 17:05 Mogsdad 45.8k2121 gold badges163163 silver badges285285 bronze badges asked May 5, 2015 at 12:41 helloworldhelloworld 2,32133 gold badges2626 silver badges4040 bronze badges 4

• Show your stringify code. Its not there – Zig Mandel Commented May 5, 2015 at 13:15
• Seems that I found the issue. I'm using associative arrays and JSON.stringify saves them as [] instead of full array. But if I use [{key:value}] I'll need to do iterations to find things which will waste the whole activity of performance gain – helloworld Commented May 5, 2015 at 13:18
• Json.stringify perfectly saves any object. Your issue cant be that. – Zig Mandel Commented May 5, 2015 at 13:29
• @Spencer picked up the missing thread - you keep saying "Array", when you mean "associative array", which is actually an object with named properties. (You should have showed more code, to make your question clearer. That's why SO asks for the "minimal code required to replicate the problem".) – Mogsdad Commented May 5, 2015 at 14:30

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 5

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

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 ment 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.

本文标签： javascriptSaving an array globallyStack Overflow