带有Sheets.Spreadsheets.BatchUpdate的JSON有效载荷无效 [英] Invalid JSON Payload with Sheets.Spreadsheets.BatchUpdate
问题描述
尝试使用Sheets.Spreadsheets.Get和Sheets.Spreadsheets.Batchupdate.我正在尝试从一个电子表格中获取提取格式并将其粘贴到另一个电子表格中.这仅仅是进一步应用的概念证明.我收到了以下代码的JSON有效负载错误，看不到如何格式化以插入Array.
Trying to work with Sheets.Spreadsheets.Get and Sheets.Spreadsheets.Batchupdate. I'm trying to get pull formatting from one spreadsheet and paste that formatting to another. This is simply a proof of concept for further application. I get a JSON payload error with the following code and can't see to figure out how to format it to insert the 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)
}
推荐答案
问题:
您正在通过电子表格资源( TestArray ，由 spreadsheets.get返回)，您应该在其中提供颜色.因此，您将收到无效的JSON有效负载错误.
Issue:
You are supplying at Spreadsheet resource (TestArray, returned by spreadsheets.get) where you should provide a color. Hence, you are getting an invalid JSON payload error.
这是因为 fields 参数将过滤在您的第一次调用的响应中将填充哪些嵌套字段，但是这些嵌套字段仍将嵌套在JSON上，因此您必须通过指定相应的父级属性来访问它们.
This is because the fields parameter will filter which nested fields will be populated in the response of your first call, but these nested fields will still be nested on your JSON, and you'll have to access them by specifying the corresponding parent properties.
您的第一个电话的响应类似于:
The response to your first call is something like:
{
"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 ，则应执行以下操作:
So, for example, if you want to access the backgroundColor of the first cell of the first row in the requested range, you should do the following:
var backgroundColor = TestArray["sheets"][0]["data"][0]["rowData"][0]
["values"][0]["effectiveFormat"]["backgroundColor"];
或者，或者，如果您要检索请求范围内所有单元格的 backgroundColors 的2D数组，则可以执行以下操作:
Or, alternatively, if you want to retrieve a 2D array of the backgroundColors of all the cells in the requested range, you could do this:
var backgroundColors = TestArray["sheets"][0]["data"][0]["rowData"]
.map(row => row["values"]
.map(value => value["effectiveFormat"]["backgroundColor"]));
如果要更新多个单元格，则需要相应地编辑请求正文，并将其他行和 values 添加到相应的数组中.
If you want to update several cells, you would need to edit the request body accordingly, adding the additional rows and values to the corresponding arrays.
例如，如果您希望目标单元格与源单元格具有相同的背景色，并且所有单元格都具有值 Test String ，则可以这样构建请求正文:
For example, if you want the destination cells to have the same background colors as the source, and all of them to have the value Test String, you could build your request body like this:
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 中作为可选参数提供).
If each cell should have different string values, you should store those in a 2D array, and provide them inside the map methods, instead of Test String, specifying the corresponding indexes (provided as an optional parameter in each map).
为了用相同的请求更新值和背景色，您可以使用 map 遍历其中之一，并使用相应的索引参数(它们是 map 方法(在下面的示例中称为 i 和 j )来访问另一个方法的不同值.
In order to update both values and background colors with the same request, you can just iterate through one of them with map, and use the corresponding index parameters (they are optional parameters of the map method, called i and j in the sample below) to access the different values of the other one.
例如，如果要用于构建行的2D数组 backgroundColors 和 strings ，则可以执行以下操作:
For example, if backgroundColors and strings the 2D arrays which you want to use to build rows, you can do this:
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
}
}
});
}
});
这篇关于带有Sheets.Spreadsheets.BatchUpdate的JSON有效载荷无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
