Javascript 在 Google Apps Script 中创建哈希数组

Creating hash array in Google Apps Script

提问by Psyllex

I've been trying to work with Trello and the Google Apps Script this week. I am trying to create an array of hashes that I can then use to load the spreadsheet. Google apps script doesn't like the typical javascript code of creating hashes. I've looked up the docs but they don't have anything like hashes...they say to:

这周我一直在尝试使用 Trello 和 Google Apps 脚本。我正在尝试创建一个哈希数组，然后我可以用它来加载电子表格。Google 应用程序脚本不喜欢创建哈希的典型 javascript 代码。我查过文档，但他们没有像哈希这样的东西……他们说：

 var object = [];
 var object1 = {};
 object.push(object1);

This wont work because I'm essentially trying to do something like:

这行不通，因为我本质上是在尝试执行以下操作：

var hash={name: , label: };
var n= someNumber;
var l= someLabel
var hash.push(name: n, label: l);

Essentially that is the code I have right now. But here is my entire function:

基本上这就是我现在拥有的代码。但这是我的全部功能：

  function getData(){
  var list={};
  //get the list of delivered cards from Trello
  var listRequest = authorizeToTrello(); // get authorization
  var result = UrlFetchApp.fetch("https://trello.com/1/lists/4fea3a2c3a7038911ebff2d8/cards",
  listRequest);//fetch list
  var listOfCards = Utilities.jsonParse(result.getContentText());//Google app utility format json

  //outer loop to iterate through list of Cards
  for(var i=0; i < listOfCards.length; i++){
     var cardId = listOfCards[i].id; //get the id of a single card
     var l = listOfCards[i]["label"]; //get the label for the our structure

  //get a json object for a single card within the list of cards iteration 
  var cardRequest = authorizeToTrello();
  var getCard = UrlFetchApp.fetch("https://trello.com/1/cards/" + cardId + "/actions", cardRequest); 
  var singleCard = Utilities.jsonParse(getCard.getContentText());

 //inner loop to iterate the single cards JSON objects

  for(var j=0; j < singleCard.length; j++) {
    if(singleCard[j].data != undefined && singleCard[j].data.listAfter != undefined)
    {
      var str = singleCard[j]["data"]["listAfter"]['name'];
      if(str === "Delivered Q3 2012"){
          var n = singleCard[j]['memberCreator']['fullName'];
        }
     }
  }
    //push the data to list
    list.push(n,l);
  }

 return name, label; //return list for output
 }

回答by megabyte1024

Reading the question, I understood that the author needs to know how to create an associative arrayin a GAS. If it is correct then here is a couple of links (hereand here) and a sample code is bellow.

读了这个问题，我明白了作者需要知道如何在 GAS 中创建关联数组。如果它是正确的，那么这里有几个链接（这里和这里），下面是一个示例代码。

function testMap() {
  var map = {};
  map["name1"] = "value1";
  map["name2"] = "value2";
  return map;
}

If the author needs really

如果作者真的需要

an array of hashes

哈希数组

then there are a couple of ways depending on which hash algorithm is required.

然后有几种方法取决于需要哪种哈希算法。

1. to use the Utilities.computeDigestmethod to calculate a hash of a string using one of available algorithms.
2. if the required hash calculation algorithm is not supported by the Utilities.computeDigest, then is possible to write own implementation as it is donefor the BLAKE function.

1. 使用Utilities.computeDigest方法使用可用算法之一计算字符串的哈希值。
2. 如果Utilities.computeDigest不支持所需的哈希计算算法，则可以编写自己的实现，就像为BLAKE 函数所做的那样。

Here is a sample of how to create an array of hashesusing the MD5 hash.

以下是如何使用 MD5 散列创建散列数组的示例。

function testHash() {
  var array = [];
  array.push(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, "value1"));
  array.push(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, "value2"));
  return array;
}

P.S. The return line of the author code return name, label; //return list for output is not correct - only the labelvariable value is returned. To return a couple of variables as an array is necessary to write return [name, label];. Or may be the author needs to return the listvariable and not nameand label.

PS作者代码的返回行return name, label; //return list for output 不正确-仅label返回变量值。要将几个变量作为数组返回，必须编写return [name, label];. 或者可能是作者需要返回list变量而不是nameand label。

回答by kkoolpatz

I know this is an old post / question, but i would like to update my answer since the original anwer (1st answer) is misleading. I was myself looking for how to return associative arrays back to a cell in the spreadsheet, but alas.. "YOU CANNOT". Google spreadsheet MUST want an numerically indexed array or an object. Otherwise it returns "#ERROR".

我知道这是一个旧帖子/问题，但我想更新我的答案，因为最初的答案（第一个答案）具有误导性。我自己正在寻找如何将关联数组返回到电子表格中的单元格，但是唉......“你不能”。Google 电子表格必须需要一个数字索引数组或一个对象。否则返回“#ERROR”。

Here are the steps to replicate the issue.

以下是重现问题的步骤。

function testMap() {
  var map = {};
  map["name1"] = "value1";
  map["name2"] = "value2";
  return map

Formula in your cell: =testMap()
Value in your cell: Thinking... #ERROR

Solution (rather a workaround)

解决方案（而是一种解决方法）

1: Transfer your objects from your associative array into a numerically indexed array using for-each type loop.

1：使用 for-each 类型循环将对象从关联数组转移到数字索引数组中。

var temp = new Array();
for (var i in map) {
    temp.push([i,map[i]])
    // optionally use activeSheet.getRange(X:X).setValue([i,map[i]])) function here.
   // set values will not work in cell functions. To use it via cell functions, rerun / trigger the functions using an on_edit event. 
}

If you used a temp like numerically indexed array, you can return "temp" back to the calling cell.

如果您使用了类似数字索引数组的 temp，则可以将“temp”返回给调用单元格。

回答by Steven.AA

Summary: For onEdit()purposes, use Cache Service to define associative array data.

摘要：出于onEdit()目的，使用缓存服务来定义关联数组数据。

Here's a shared Gsheetdemonstrating this curious behavior. I tried the following solution in programmatically defining an associative array based on data in a Google sheet.

这是一个共享的 Gsheet，展示了这种奇怪的行为。我在基于 Google 工作表中的数据以编程方式定义关联数组时尝试了以下解决方案。

var assocArr = {
  labels: {},
  init: function () {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheetName');
    var values = sheet.getDataRange().getValues();
    for(var row in values) {
      assocArr.labels[values[row][0]] = values[row][1];
    };
    for(var key in assocArr.labels) {
      Logger.log("key: %s, value: %s",key, assocArr.labels[key]);
    };
    return(void(0));
  },
};

To execute this, you run the init()method in the onOpen()event handler.

要执行此操作，请init()在onOpen()事件处理程序中运行该方法。

function onOpen() {
  assocArr.init();
  var key = 'test';
  SpreadsheetApp.getUi().alert( assocArr.labels[key] );
  Logger.log("onOpen: key: %s, value: %s",key, assocArr.labels[key]);
};

The logger message confirms that init()loads the data from the worksheet.

记录器消息确认init()从工作表加载数据。

Now if I try to reference this assocArr object in onEdit()it returns undefinedfor all key values.

现在，如果我尝试引用这个 assocArr 对象，onEdit()它会返回undefined所有键值。

function onEdit(event) {
  var key = 'test';
  SpreadsheetApp.getUi().alert( assocArr.labels[key] );
  Logger.log("onEdit: key: %s, value: %s",key, assocArr.labels[key]);
};

I infer that for security reasons, Google limited the simple-trigger onEdit()to not have global variable scope, same as they voided the utility of the event.userproperty.

我推断出于安全原因，谷歌将简单触发器限制onEdit()为没有全局变量范围，因为它们使event.user属性的效用无效。

Now instead if I simply put the key-value pair in the cache, it works! Here is the complete code that works using the Cache Service.

现在，如果我只是将键值对放在缓存中，它就可以工作！这是使用缓存服务工作的完整代码。

var cache = CacheService.getPrivateCache();

var assocArr = {
  init: function () {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Account Labels');
    var values = sheet.getDataRange().getValues();
    for(var row in values) {
      cache.put(values[row][0], values[row][1], 3600);
    };
    return(void(0));
  },
};

function onOpen() {
  assocArr.init();
  var key = 'test';
  SpreadsheetApp.getUi().alert( cache.get(key) );
  Logger.log("onOpen: key: %s, value: %s",key, cache.get(key));
};


function onEdit(event) {
  var key = 'test';
  SpreadsheetApp.getUi().alert( cache.get(key) );
  Logger.log("onEdit: key: %s, value: %s",key, cache.get(key));
};

Curiously, the onEdit()has the cachevariable in its scope.

奇怪的是， 的作用域中onEdit()有cache变量。

Here again is the shared Gsheetdemonstrating this curious behavior.

这里再次是共享 Gsheet展示了这种奇怪的行为。