全局保存数组 [英] Saving an array globally
问题描述
我有一张包含两张 A $ c的电子表格$ c>&
B
。
-
A
有2列 - 名称,金额(主列表) -
B有4列 - 名称,金额,X,Y(交易清单)
名称 code>表B 参考数据的表A
的名称列。每当选择一个名称时,我想在 B
中填充Amount列,其中列中的金额为表A
作为占位符哪些用户可以覆盖。为此,我打算在数组中加载 Sheet A
数据(全局可用),以便在 onEdit(e)
我可以引用该数组而不是访问表B
。
但是我可以找到的选项 - CacheService
和 PropertyService
- 仅保存字符串值。但我想要:
$ b
var myGlobalArray = [] ;
函数init(){
//迭代并填充数组,使其具有以下输出
// myGlobalArray [Name1] = 1
// myGlobalArray [Name2] = 2
// myGlobalArray [Name3] = 3
}
函数onEdit(e){
//如果选择的值是name1,则填充myGolbalArray [Name1]金额
}
问题
凡&如何定义myGlobalArray?
我尝试使用缓存服务,使用 JSON.Stringify
和 JSON.parse
但是 onEdit
中的数组是空的。
对脚本的每次调用都会使用自己的唯一全局变量创建脚本的新实例。每次你调用一个脚本时,你都会为这个特定的实例找到一个全局的this。您将PropertyService看作是保存数据的一种持久方式是正确的。
马上看到您的globalArray没有设置正确:
var myGlobalArray = [];
必须是
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};
现在可以保存并从属性商店中读取。
PropertiesService.getScriptProperties()。setProperty(NameArray,stringArray);
stringArray = PropertiesService.getScriptProperties()。getProperty(NameArray);
myGlobalArray = JSON.parse(stringArray);
Logger.log(myGlobalArray ['name1'])//返回1
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
.
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屋!