JSON Payload กับชีตสเปรดชีตไม่ถูกต้องatchUpdate

Question

กำลังพยายามทำงานกับชีตสเปรดชีตรับและชีตสเปรดชีต.Batchupdate ฉันกำลังพยายามดึงการจัดรูปแบบจากสเปรดชีตหนึ่งและวางการจัดรูปแบบนั้นไปยังอีกสเปรดชีต นี่เป็นเพียงการพิสูจน์แนวคิดสำหรับการประยุกต์ใช้ต่อไป ฉันได้รับข้อผิดพลาด JSON payload พร้อมรหัสต่อไปนี้และไม่เห็นวิธีจัดรูปแบบเพื่อแทรก Array

function Test() {
 //sheets[].data[].rowData[].values[].cellData.effectiveFormat.backgroundColor
 var TestArray = Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", {
   ranges:"Awesome!A1:C3",
   fields:"sheets(data(rowData(values(effectiveFormat.backgroundColor))))"
 });
 
var spreadsheetId = "1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg";
 var result = Sheets.Spreadsheets.batchUpdate({
   requests: [{
     updateCells: {
       rows: [{
         values: [{
           userEnteredValue: {
             stringValue: 'Test String'
           }, userEnteredFormat: {
               backgroundColor: TestArray
             }
         }]
       }],//rows
       fields: 'userEnteredValue.stringValue,userEnteredFormat.backgroundColor',
       start: {
         sheetId: 1616717220,
         rowIndex: 0,
         columnIndex: 0
       }
     }//update cell
   }]//requests
 }, spreadsheetId)
}  ```

**EDIT:**

Rebuilt function copying both Text and Background colors. 

function myFunction() {

var TestArray = Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", {
   ranges:"Awesome!A1:C3",
   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 TotalText = Sheets.Spreadsheets.Values.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", "Awesome!A1:C3").values; 
 
//Map Text
var textrows = TotalText.map(rowText => {
 return {
   values: rowText.map(cellText => {
     return {
       userEnteredValue: {
         stringValue: cellText         
       }
     }       
   })
 }
})

//Map Background Colors
var colorrows = backgroundColors.map(rowColors => {
 return {
   values: rowColors.map(cellColor => {
     return {
       userEnteredFormat: {
         backgroundColor: cellColor        
       }       
     }             
   })
 }
})

var spreadsheetId = "1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg";
var result = Sheets.Spreadsheets.batchUpdate({
  requests: [{
    updateCells: {
      rows: textrows,
      fields: 'userEnteredValue.stringValue',
      start: {
        sheetId: 1616717220,
        rowIndex: 0,
        columnIndex: 0
      }
    }//update cell
  },{
    updateCells: {
      rows: colorrows,
      fields: 'userEnteredFormat.backgroundColor',
      start: {
        sheetId: 1616717220,
        rowIndex: 0,
        columnIndex: 0
      }
    }
  }]
}, spreadsheetId)
}


Edit #2:

   function myFunctionOneRequest() {


var TestArray = Sheets.Spreadsheets.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", {
   ranges:"Awesome!A1:C3",
   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 TotalText = Sheets.Spreadsheets.Values.get("1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg", "Awesome!A1:C3").values; 

 
//Map Text
var textrows = TotalText.map((rowText,i) => {
 return {
   values: rowText.map((cellText,j) => {
     return {
       userEnteredValue: {
         stringValue: cellText         
       }
     }       
   })
 }
})

//Map Background Colors
var colorrows = backgroundColors.map((rowColors,k) => {
 return {
   values: rowColors.map((cellColor,l) => {
     return {
       userEnteredFormat: {
         backgroundColor: cellColor        
       }       
     }             
   })
 }
})

var spreadsheetId = "1eAq-RbtrCSMRPZ0p7XIpG3vd29yL-3SQ3D3JGyiUhKg";
var result = Sheets.Spreadsheets.batchUpdate({
  requests: [{
    updateCells: {
      rows: textrows,
      fields: 'userEnteredValue.stringValue',
      start: {
        sheetId: 1616717220,
        rowIndex: 0,
        columnIndex: 0
      }
    }//update cell
  }]
}, spreadsheetId)
}

Answer 1

ปัญหา:

คุณจะจัดหาที่ทรัพยากร Spreadsheet ( TestArrayกลับโดยspreadsheets.get ) ซึ่งคุณควรให้สี ดังนั้นคุณได้รับข้อผิดพลาดเพย์โหลด JSON ที่ไม่ถูกต้อง

เนื่องจากfieldsพารามิเตอร์จะกรองฟิลด์ที่ซ้อนกันซึ่งจะถูกเติมในการตอบสนองของการโทรครั้งแรกของคุณ แต่ฟิลด์ที่ซ้อนกันเหล่านี้จะยังคงซ้อนอยู่บน JSON ของคุณและคุณจะต้องเข้าถึงฟิลด์เหล่านี้โดยระบุคุณสมบัติหลักที่เกี่ยวข้อง

วิธีการแก้:

การตอบสนองต่อการโทรครั้งแรกของคุณมีลักษณะดังนี้:

{
  "sheets": [
    {
      "data": [
        {
          "rowData": [
            {
              "values": [
                {
                  "effectiveFormat": {
                    "backgroundColor": {
                      "red": 1,
                      "green": 1,
                      "blue": 1
                    }
                  }
                },
                // Other cells in row
              ]
            },
            // Other rows in the requested range
          ]
        } // Only one range is specified, so there's only one GridData element
      ]
    },
    // Other sheets
  ]
}

ตัวอย่างเช่นหากคุณต้องการเข้าถึงbackgroundColorเซลล์แรกของแถวแรกในช่วงที่ร้องขอคุณควรทำสิ่งต่อไปนี้:

var backgroundColor = TestArray["sheets"][0]["data"][0]["rowData"][0]
                                 ["values"][0]["effectiveFormat"]["backgroundColor"];

หรือหากคุณต้องการดึงข้อมูลอาร์เรย์ 2 มิติของbackgroundColorsเซลล์ทั้งหมดในช่วงที่ร้องขอคุณสามารถทำได้:

var backgroundColors = TestArray["sheets"][0]["data"][0]["rowData"]
                       .map(row => row["values"]
                       .map(value => value["effectiveFormat"]["backgroundColor"]));

หากคุณต้องการอัปเดตเซลล์หลายเซลล์คุณจะต้องแก้ไขเนื้อหาของคำขอให้สอดคล้องกันโดยเพิ่มส่วนเพิ่มเติมrowsและvaluesในอาร์เรย์ที่เกี่ยวข้อง

แก้ไข:

ตัวอย่างเช่นหากคุณต้องการให้เซลล์ปลายทางมีสีพื้นหลังเหมือนกับที่มาและทุกเซลล์มีค่าTest Stringคุณสามารถสร้างเนื้อหาคำขอของคุณได้ดังนี้:

var rows = backgroundColors.map(rowColors => {
  return {
    values: rowColors.map(cellColor => {
      return {
        userEnteredValue: {
          stringValue: 'Test String'         
        }, 
        userEnteredFormat: {
          backgroundColor: cellColor        
        }       
      }             
    })
  }
})
var result = Sheets.Spreadsheets.batchUpdate({
   requests: [{
     updateCells: {
       rows: rows,
       fields: 'userEnteredValue.stringValue,userEnteredFormat.backgroundColor',
       start: {
         sheetId: 1616717220,
         rowIndex: 0,
         columnIndex: 0
       }
     }//update cell
   }]//requests
}, spreadsheetId)

หากแต่ละเซลล์ควรมีค่าสตริงที่แตกต่างกันคุณควรเก็บค่าเหล่านั้นไว้ในอาร์เรย์ 2 มิติและระบุไว้ในmapเมธอดแทนการTest Stringระบุดัชนีที่เกี่ยวข้อง (ให้เป็นพารามิเตอร์ทางเลือกในแต่ละรายการmap)

แก้ไข 2:

ในการอัปเดตทั้งค่าและสีพื้นหลังด้วยคำขอเดียวกันคุณสามารถทำซ้ำผ่านหนึ่งในนั้นด้วยmapและใช้พารามิเตอร์ดัชนีที่เกี่ยวข้อง (เป็นพารามิเตอร์ทางเลือกของmapวิธีการที่เรียกว่าiและjในตัวอย่างด้านล่าง) เพื่อเข้าถึงที่แตกต่างกัน ค่าของอีกค่าหนึ่ง

ตัวอย่างเช่นถ้าbackgroundColorsและstringsอาร์เรย์ 2 มิติที่คุณต้องการใช้สร้างrowsคุณสามารถทำได้:

var backgroundColors = [["2D array with colors"]];
var strings = [["2D array with strings"]];
var rows = backgroundColors.map((rowColors,i) => {
  return {
    values: rowColors.map((cellColor,j) => {
      return {
        userEnteredValue: {
          stringValue: strings[i][j]       
        }, 
        userEnteredFormat: {
          backgroundColor: cellColor        
        }       
      }             
    });
  }
});