Google App脚本-映射时发生错误-TypeError:无法读取未定义的属性"map" [英] Google App Script - Error when Mapping - TypeError: Cannot read property 'map' of undefined
问题描述
With the guidance of the community I've recently learned here about BatchUpdating background colors of a given google sheet.
我尝试将其应用于我的实际工作表,但遇到错误代码. TypeError: Cannot read property 'map' of undefined
I've attempted to apply this to my actual sheet but run into the error code. TypeError: Cannot read property 'map' of undefined
我的测试表中没有问题的代码在这里:
The code in my test sheet which works without issue is here:
var TestArray = Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", {
ranges:"TestBackgroundSheet!A1:AD39", fields:"sheets(data(rowData(values(effectiveFormat.backgroundColor))))"
});
var backgroundColors = TestArray["sheets"][0]["data"][0]["rowData"]
.map(row => row["values"]
.map(value => value["effectiveFormat"]["backgroundColor"]));
然后我将这段代码复制并粘贴到我的实际项目中.为了排除故障,我什至将确切的工作表从测试项目复制到了实际项目中.代码在这里列出:
I then copied and pasted this code into my actual project. In an attempt to troubleshoot I even copied the exact Sheet from my test project into the actual project. The code is listed here:
var TestArray = Sheets.Spreadsheets.get("1pcIKNUFmkk0d-UGg1sXl5xbsJC2WhocIHpM3et-CMgo", {
ranges:"TestBackgroundSheet!A1:AD39", fields:"sheets(data(rowData(values(effectiveFormat.backgroundColor))))"
});
var backgroundColors = TestArray["sheets"][0]["data"][0]["rowData"]
.map(row => row["values"]
.map(value => value["effectiveFormat"]["backgroundColor"]));
您可以看到,除了不同的SheetID之外,代码完全相同.尽管如此,我仍然收到TypeError: Cannot read property 'map' of undefined
错误.
As you can see the code is exactly the same except for a different SheetID. Despite this i continue to get the TypeError: Cannot read property 'map' of undefined
Error.
错误代码引用的行172是.map(value => value["effectiveFormat"]["backgroundColor"]));
The error code references line 172 which is .map(value => value["effectiveFormat"]["backgroundColor"]));
编辑#2:
包含Rafa的代码后,我得到此错误代码-> SyntaxError:意外的令牌,位于JSON的位置1(第177行,文件宏")
With inclusion of Rafa's Code I get this error code -> SyntaxError: Unexpected token , in JSON at position 1 (line 177, file "macros")
包含的代码如下.
var TestArray = Sheets.Spreadsheets.get("1pcIKNUFmkk0d-UGg1sXl5xbsJC2WhocIHpM3et-CMgo", {
ranges:"TestBackgroundSheet!A1:AD39",
fields:"sheets(data(rowData(values(effectiveFormat.backgroundColor))))"
});
var rowData = TestArray["sheets"][0]["data"][0]["rowData"]
.map(row => row.getValues()).toString()
var backgroundColors = JSON.parse("[" + rowData + "]")
.map(value => {
let v = value["effectiveFormat"]
return v ? v["backgroundColor"] : null
})
编辑#3:
具有以下代码的RowData记录器
Logger of RowData with below code
for (var x = 0; x < 40; x++) {
Logger.log(x + JSON.stringify(TestArray["sheets"][0]["data"][0]["rowData"][x]));
}
我可以看到问题出在Logger语句的前四行.而测试表"中的空行则返回白色背景颜色的完整行(RGB {绿色":1,红色":1,蓝色":1}).我在前4行中的3行中得到了一个空对象.我在下面包括了输出的前四行.我不确定为什么会这样.
I can see the issue is in the first four lines of Logger statements. While the empty rows in the "Testing Sheet" returns full rows of White background color (RGB {"green":1,"red":1,"blue":1}) In the "Reat Sheet" I get an empty object for 3 of the first 4 lines. I've include the first four lines out output below. I'm not sure why this occurs.
实际:
[20-10-21 08:53:23:591 EDT] 0{}
[20-10-21 08:53:23:593 EDT] 1{"values":[{},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}}]}
[20-10-21 08:53:23:595 EDT] 2{}
[20-10-21 08:53:23:597 EDT] 3{}
[20-10-21 08:53:23:600 EDT] 4{"values":[{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}}]}
测试:
[20-10-21 05:53:14:167 PDT] 0{"values":[{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}}]}
[20-10-21 05:53:14:170 PDT] 1{"values":[{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}}]}
[20-10-21 05:53:14:193 PDT] 2{"values":[{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}}]}
[20-10-21 05:53:14:195 PDT] 3{"values":[{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}}]}
[20-10-21 05:53:14:198 PDT] 4{"values":[{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"blue":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"blue":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"red":1,"green":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"green":1,"red":1,"blue":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"red":1,"green":1}}},{"effectiveFormat":{"backgroundColor":{"blue":1,"green":1,"red":1}}}]}
推荐答案
答案:
并非row["values"]
的所有元素都具有数据-因此,当该行没有数据时,您将无法运行.map(value => value["effectiveFormat"]["backgroundColor"])
.
Answer:
Not all elements of row["values"]
have data - and so you can't run .map(value => value["effectiveFormat"]["backgroundColor"])
when that row doesn't have data.
您已经使用以下方法从API获取数据
You've got your data from the API using
Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg")
,并使用您的场遮罩对其进行相应过滤.您作为响应而获得的数据将是工作表中的所有内容-甚至是没有背景色数据的单元格.结果,您不能像这样映射每一行,因为您将尝试引用effectiveFormat
元素,而该元素根本不存在.
and filtered it accordingly with your field mask. The data you get as a response will be everything in the sheet - even the cells that do not have data for background colour. As a result of this, you can not map each row like this, as you will try and reference the effectiveFormat
element where it simply doesn't exist.
您可以使用三元运算符解决此问题;如果元素value["effectiveFormat"]
不存在,则可以简单地返回null
:
You can use the ternary operator to solve this; if the element value["effectiveFormat"]
doesn't exist you can simply return null
:
var rowData = TestArray["sheets"][0]["data"][0]["rowData"]
.map(row => row.getValues()).toString()
var backgroundColors = JSON.parse("[" + rowData + "]")
.map(value => {
let v = value["effectiveFormat"]
return v ? v["backgroundColor"] : null
})
注意::API还会在响应中的JSON对象中返回函数,您可以在Apps脚本中使用这些函数.这很方便,因为直接引用row["values"]
可以返回对象而不是数据:
NB: The API also returns functions within the JSON objects in the response which you can use in Apps Script. This comes in handy because referencing row["values"]
directly can return the object rather than the data:
console.log(TestArray["sheets"][0]["data"][0]["rowData"]
.map(row => row["values"]))
产量:
[
{
setPivotTable: [Function],
getDataSourceTable: [Function],
getDataValidation: [Function],
getEffectiveValue: [Function],
setNote: [Function],
setFormattedValue: [Function],
getTextFormatRuns: [Function],
setUserEnteredFormat: [Function],
toString: [Function],
getFormattedValue: [Function],
setEffectiveFormat: [Function],
effectiveFormat: [Object],
setDataSourceFormula: [Function],
getPivotTable: [Function],
setUserEnteredValue: [Function],
setDataValidation: [Function],
setDataSourceTable: [Function],
getUserEnteredFormat: [Function],
setEffectiveValue: [Function],
getEffectiveFormat: [Function],
getHyperlink: [Function],
getNote: [Function],
setHyperlink: [Function],
getUserEnteredValue: [Function],
setTextFormatRuns: [Function],
getDataSourceFormula: [Function]
},
...
]
第一个.map(row => row.getValues())
之后的toString()
调用超出了此范围,然后将数据放回到第二个映射函数中.
The toString()
call after the first .map(row => row.getValues())
gets past this before putting the data back into the second mapping function.
希望对您有帮助!
这篇关于Google App脚本-映射时发生错误-TypeError:无法读取未定义的属性"map"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!