Google App Script-매핑시 오류-TypeError : 정의되지 않은 'map'속성을 읽을 수 없습니다.

debugcn 에 게시 Dev

창

커뮤니티의 안내에 따라 최근 여기 에서 주어진 Google 시트의 BatchUpdating 배경색에 대해 배웠습니다 .

나는 이것을 실제 시트에 적용하려고 시도했지만 오류 코드가 발생했습니다. TypeError: Cannot read property 'map' of undefined

문제없이 작동하는 테스트 시트의 코드는 다음과 같습니다.

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

그런 다음이 코드를 실제 프로젝트에 복사하여 붙여 넣었습니다. 문제를 해결하기 위해 테스트 프로젝트에서 실제 프로젝트로 정확한 시트를 복사했습니다. 코드는 다음과 같습니다.

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오류가 계속 발생합니다 .

오류 코드는 172 행을 참조합니다. .map(value => value["effectiveFormat"]["backgroundColor"]));

편집하다:

편집 # 2 :

Rafa의 코드를 포함하면이 오류 코드-> SyntaxError : Unexpected token, JSON 위치 1 (177 줄, 파일 "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의 로거

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

Logger 문의 처음 네 줄에 문제가 있음을 알 수 있습니다. "Testing Sheet"의 빈 행은 흰색 배경색의 전체 행을 반환하지만 (RGB { "green": 1, "red": 1, "blue": 1}) "Reat Sheet"에서 빈 개체를 얻습니다. 처음 4 줄 중 3 줄. 아래에 처음 네 줄의 출력을 포함했습니다. 왜 이런 일이 발생하는지 잘 모르겠습니다.

실제 :

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

라파 기예르모

대답:

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

참고 : API는 또한 Apps Script에서 사용할 수있는 응답의 JSON 개체 내의 함수를 반환합니다. 참조 row["values"]는 데이터가 아닌 객체를 직접 반환 할 수 있기 때문에 유용 합니다.

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

toString()첫 번째 이후 의 호출 .map(row => row.getValues())은 데이터를 두 번째 매핑 함수에 다시 넣기 전에 이것을지나갑니다.

도움이 되었기를 바랍니다.

참조 :

조건부 (삼항) 연산자-JavaScript | MDN

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-04-5

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사