Неверная полезная нагрузка JSON с Sheets.Spreadsheets.BatchUpdate

Пытаюсь работать с Sheets.Spreadsheets.Get и Sheets.Spreadsheets.Batchupdate. Я пытаюсь получить форматирование из одной электронной таблицы и вставить это форматирование в другую. Это просто проверка концепции для дальнейшего применения. Я получаю сообщение об ошибке полезной нагрузки JSON со следующим кодом и не могу понять, как отформатировать его для вставки массива.


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

person LANCE    schedule 04.10.2020    source источник
comment
Выстрел в темноте... Я бы попробовал удалить комментарии из json.   -  person Yuri Khristich    schedule 05.10.2020
comment
Привет, я опубликовал ответ по этому поводу. Дайте мне знать, если вам нужны дополнительные указания относительно того, как создать тело запроса для вашего batchUpdate после получения backgroundColors для каждой ячейки в запрошенном диапазоне.   -  person Iamblichus    schedule 05.10.2020


Ответы (1)


Проблема:

Вы предоставляете ресурс электронных таблиц (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"];

Или, в качестве альтернативы, если вы хотите получить двумерный массив 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)

Если каждая ячейка должна иметь разные строковые значения, вы должны сохранить их в 2D-массиве и предоставить их внутри методов map вместо Test String, указав соответствующие индексы (предоставленные как необязательный параметр в каждом map).

Редактировать 2:

Чтобы обновить как значения, так и цвета фона с помощью одного и того же запроса, вы можете просто выполнить итерацию по одному из них с помощью map и использовать соответствующие параметры индекса (это необязательные параметры метода map, называемые i и j в приведенном ниже примере). для доступа к различным значениям другого.

Например, если backgroundColors и strings 2D-массивы, которые вы хотите использовать для построения 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        
        }       
      }             
    });
  }
});
person Iamblichus    schedule 05.10.2020
comment
Спасибо за повтор. если вы хотите получить 2D-массив backgroundColors всех ячеек в запрошенном диапазоне - я считаю, что это именно то направление, которое мне нужно. Я не знаком с .map и тем, как он работает. Теперь мне нужно кое-что прочитать и проверить! - person LANCE; 06.10.2020
comment
@LANCE Пожалуйста. Если вам нужна дополнительная помощь в создании тела запроса с соответствующим backgroundColors, дайте мне знать. Если предложенное решение работает для вас, рассмотрите возможность принятия этого ответа. - person Iamblichus; 06.10.2020
comment
@lamblichus - Было бы здорово получить некоторые рекомендации по созданию тела запроса с использованием backgroundColors. - person LANCE; 06.10.2020
comment
@LANCE Я отредактировал свой ответ, указав пример того, как создать тело запроса, чтобы скопировать цвета фона из исходного диапазона. Дайте мне знать, если это решит вашу проблему. - person Iamblichus; 07.10.2020
comment
@LANCE Кроме того, дайте мне знать, если у вас возникнут проблемы с установкой строковых значений в диапазон назначения. - person Iamblichus; 07.10.2020
comment
@Lamblichus - Спасибо за помощь. Используя ваш код, я улучшил свое понимание .map fxn. Я добавил редактирование к своему исходному вопросу с перестроенным кодом. Я наметил текстовый массив, а затем отдельно цветовой массив. Я использовал один пакетный запрос, но мне понадобились два отдельных запроса. Это самый эффективный способ сделать это? Это большой шаг для меня, и я очень горжусь прогрессом. Еще раз спасибо за помощь. - person LANCE; 07.10.2020
comment
@LANCE Вы можете сделать это одним запросом. Просто укажите аргумент индекса для каждой карты (например, backgroundColors.map((rowColors,i) => {...} , rowColors.map((cellColor,j) => {...}) и укажите эти индексы при указании stringValue. Например, если 2D_Strings — это переменная, в которой вы сохранили различные текстовые значения, используйте stringValue: 2D_Strings[i][j]. - person Iamblichus; 08.10.2020
comment
@Lamblichus - я был бы признателен за дополнительные указания на этом этапе. Я проиндексировал обе переменные для хранения цвета и текста, как вы предложили. Я не понимаю, как настроить мой запрос, чтобы включить как цветовые, так и текстовые строки в строки: раздел запроса. Я прикрепил свою текущую версию кода как «Редактировать № 2» внизу исходного вопроса. - person LANCE; 12.10.2020
comment
@LANCE Я снова отредактировал свой ответ (см. Edit 2), чтобы более подробно показать вам, как это сделать. - person Iamblichus; 13.10.2020
comment
Код работает. Я ценю все ваши указания на протяжении всего этого. Просто пытаюсь понять код полностью. Вы сопоставляете массив Backgroundcolors, а затем «stringValue: 2D_Strings[i][j]» применяет эти индексы к массиву строк? Я протестировал, заменив и сопоставив строки и применив индексы к BackgroundColors. Это всегда можно обменять? - person LANCE; 13.10.2020
comment
@LANCE Да, это можно поменять местами. Индексы i,j работают для обоих двумерных массивов. - person Iamblichus; 14.10.2020
comment
@lamblichus - я попытался расширить диапазон исходного массива и столкнулся с этой ошибкой. «Ошибка типа: невозможно прочитать свойство backgroundColor неопределенного». Это происходит с этой строкой кода -› '.map(value =› value[efficientFormat][backgroundColor]));». После некоторых исследований я думаю, что это связано с тем, что массив не полностью заполнен. Несмотря на то, что я использую массив цветов фона, это происходит, если есть пустые ячейки, но не в том случае, если каждая ячейка в диапазоне содержит текст. Есть ли какая-то проверка ошибок для обработки пустого значения? Почему отсутствие текста вызывает беспокойство? - person LANCE; 19.10.2020
comment
@LANCE Поскольку ваш первоначальный вопрос был решен, я предлагаю вам опубликовать новый, чтобы решить эту новую проблему, с которой вы столкнулись. - person Iamblichus; 19.10.2020