Google App Script - Error when Mapping - TypeError: Tidak dapat membaca 'peta' properti yang tidak ditentukan

Question

Dengan panduan komunitas yang baru-baru ini saya pelajari di sini tentang BatchUpdating warna latar belakang dari lembar Google tertentu.

Saya telah mencoba menerapkan ini ke lembar saya yang sebenarnya tetapi mengalami kode kesalahan. TypeError: Cannot read property 'map' of undefined

Kode di lembar tes saya yang berfungsi tanpa masalah ada di sini:

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

Saya kemudian menyalin dan menempelkan kode ini ke dalam proyek saya yang sebenarnya. Dalam upaya untuk memecahkan masalah, saya bahkan menyalin Lembar yang tepat dari proyek uji saya ke dalam proyek yang sebenarnya. Kode tersebut tercantum di sini:

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

Seperti yang Anda lihat, kodenya persis sama kecuali untuk SheetID yang berbeda. Meskipun demikian, saya terus mendapatkan TypeError: Cannot read property 'map' of undefinedError.

Referensi kode kesalahan baris 172 yaitu .map(value => value["effectiveFormat"]["backgroundColor"]));

Edit:

Edit # 2:

Dengan dimasukkannya Kode Rafa saya mendapatkan kode kesalahan ini -> SyntaxError: Token tak terduga, di JSON pada posisi 1 (baris 177, file "makro")

Kode yang disertakan di bawah.

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:

Logger dari RowData dengan kode di bawah ini

for (var x = 0; x < 40; x++) {
      Logger.log(x + JSON.stringify(TestArray["sheets"][0]["data"][0]["rowData"][x]));
    }

Saya bisa melihat masalahnya ada di empat baris pertama pernyataan Logger. Sementara baris kosong di "Lembar Pengujian" mengembalikan baris penuh warna latar belakang Putih (RGB {"hijau": 1, "merah": 1, "biru": 1}) Di "Lembar Reat" saya mendapatkan objek kosong untuk 3 dari 4 baris pertama. Saya telah menyertakan empat baris pertama dari keluaran di bawah ini. Saya tidak yakin mengapa ini terjadi.

Sebenarnya:

[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}}}]}

Pengujian:

[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}}}]}

Answer 1

Menjawab:

Tidak semua elemen row["values"]memiliki data - sehingga Anda tidak dapat menjalankan .map(value => value["effectiveFormat"]["backgroundColor"])saat baris tersebut tidak memiliki data.

Informasi Lebih Lanjut:

Anda mendapatkan data Anda dari API menggunakan

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

dan memfilternya sesuai dengan field mask Anda. Data yang Anda dapatkan sebagai respons adalah segalanya di sheet - bahkan sel yang tidak memiliki data untuk warna latar belakang. Akibatnya, Anda tidak dapat memetakan setiap baris seperti ini, karena Anda akan mencoba dan mereferensikan effectiveFormatelemen yang sebenarnya tidak ada.

Memperbaiki:

Anda dapat menggunakan operator terner untuk menyelesaikan ini; jika elemen value["effectiveFormat"]tidak ada, Anda dapat mengembalikan 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: API juga mengembalikan fungsi dalam objek JSON dalam respons yang dapat Anda gunakan dalam Apps Script. Ini berguna karena mereferensikan row["values"]secara langsung dapat mengembalikan objek daripada data:

console.log(TestArray["sheets"][0]["data"][0]["rowData"]
            .map(row => row["values"]))

hasil:

[ 
  { 
    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()panggilan setelah yang pertama .map(row => row.getValues())akan melewati ini sebelum menempatkan kembali data ke dalam fungsi pemetaan kedua.

Saya harap ini dapat membantu anda!

Referensi:

• Operator bersyarat (terner) - JavaScript | MDN