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. I've attempted to apply this to my actual sheet but run

Solution 1:

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.

More Information:

You've got your data from the API using

Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg")

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.

Fix:

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
    })

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"]))

yields:

[ 
  { 
    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] 
  },
  ...
]

The toString() call after the first .map(row => row.getValues()) gets past this before putting the data back into the second mapping function.

References:

• Conditional (ternary) operator - JavaScript | MDN