Thanks to the amazing insight from the community, I recently discovered this helpful tip on BatchUpdating background colors of a specific google sheet.
Excited to apply this to my own sheet, I encountered an error saying:
TypeError: Cannot read property 'map' of undefinedThe code worked flawlessly in my test sheet, as shown below:
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"]));
Upon copying and pasting the same code into my main project, which has a different SheetID, the same error persisted.
Despite following the same code structure with only a different SheetID, I repeatedly encounter the
TypeError: Cannot read property 'map' of undefinedUpon further investigation, the error seems to be centered around line 172 in the code:
.map(value => value["effectiveFormat"]["backgroundColor"]Edit: https://i.sstatic.net/FSs6B.jpg
Edit #2:
While integrating Rafa's Code into mine, a new error surfaced: SyntaxError: Unexpected token , in JSON at position 1 (line 177, file "macros")
Here is the code snippet that led to the new error:
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
})
Edit #3:
Logging RowData with the following code snippet:
for (var x = 0; x < 40; x++) {
Logger.log(x + JSON.stringify(TestArray["sheets"][0]["data"][0]["rowData"][x]));
}
Upon analysis, it seemed that the issue lied in the initial stages of the Logger statements. In the "Testing Sheet", even empty rows returned with a full White background color (RGB {"green":1,"red":1,"blue":1}), whereas in the "Real Sheet", 3 out of the first 4 lines produced an empty object. Below are the first four lines from the output, highlighting the discrepancy between the two sheets.
Actual:
[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{}
Testing:
[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":...