Payload JSON inválido com Sheets.Spreadsheets.BatchUpdate

Question

Tentando trabalhar com Sheets.Spreadsheets.Get e Sheets.Spreadsheets.Batchupdate. Estou tentando obter a formatação de uma planilha e colá-la em outra. Esta é simplesmente uma prova de conceito para aplicação posterior. Recebo um erro de carga útil JSON com o código a seguir e não consigo descobrir como formatá-lo para inserir o 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

Questão:

Você está fornecendo em Spreadsheet resource ( TestArray, retornado por spreadsheets.get ) onde você deve fornecer uma cor . Portanto, você está recebendo um erro de carga útil JSON inválido.

Isso ocorre porque o fieldsparâmetro filtrará quais campos aninhados serão preenchidos na resposta de sua primeira chamada, mas esses campos aninhados ainda serão aninhados em seu JSON e você terá que acessá-los especificando as propriedades pai correspondentes.

Solução:

A resposta à sua primeira chamada é algo como:

{
  "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
  ]
}

Assim, por exemplo, se você deseja acessar o backgroundColorda primeira célula da primeira linha no intervalo solicitado, você deve fazer o seguinte:

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

Ou, alternativamente, se quiser recuperar uma matriz 2D backgroundColorsde todas as células no intervalo solicitado, você pode fazer o seguinte:

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

Se você quiser atualizar várias células, precisará editar o corpo da solicitação de acordo, adicionando o adicional rowse valuesaos arrays correspondentes.

Editar:

Por exemplo, se você deseja que as células de destino tenham as mesmas cores de plano de fundo da origem e todas elas tenham o valor Test String, você pode criar o corpo da solicitação desta forma:

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)

Se cada célula deve ter valores de string diferentes, você deve armazená-los em um array 2D e fornecê-los dentro dos mapmétodos, em vez de Test Stringespecificar os índices correspondentes (fornecidos como um parâmetro opcional em cada um map).

Editar 2:

Para atualizar os valores e as cores de fundo com a mesma solicitação, você pode apenas iterar por meio de um deles com mape usar os parâmetros de índice correspondentes (eles são parâmetros opcionais do mapmétodo, chamados ie jno exemplo abaixo) para acessar os diferentes valores do outro.

Por exemplo, se backgroundColorse stringsas matrizes 2D que deseja usar para construir rows, você pode fazer isso:

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