0
98
VistasGoogle sheets api append multiple values with formatting
I have an array of values that i want to insert to a google sheet via API, i need to give color formatting to certain cells depending on the content of the cell like the image below:
I get to insert the values using append but i have no found any example on formatting append requests.
let values = [["Department", "Product", "Cost", "Status"],
["Clothes", "Socks", "12" "On Stock"],
["Clothes", "Hat", "15.99", "Out of stock"],
["Fresh","Apple", "18", "Pending"],
["Fresh", "Bannana", "17" "Out of stock"],
["Kitchen", "Spoon", "0.99", "Out of stock"]]
await googleSheets.spreadsheets.values.append({
auth,
spreadsheetId,
range: "Products",
valueInputOption: "USER_ENTERED",
resource: {
values: values,
},
});
What is the best approach to achieve this? batchUpdate? how would it be implemented with batchUpdate?
·
Juan Pablo Isaza
1 Respuestas
Responde la pregunta0
In your situation, how about using ConditionalFormatRule? When ConditionalFormatRule is used, when a script is run one time, the colors are automatically reflected. In order to achieve this, a sample script is as follows.
Sample script:
const googleSheets = google.sheets({ version: "v4", auth }); // Please use your authorization script.
const spreadsheetId = "###"; // Please set Spreadsheet ID.
const sheetId = "###"; // Please set Sheet ID.
// These values and colors are from your showing image.
const colorObj = [
{ value: "On Stock", rgb: [182, 215, 168] }, // #b6d7a8
{ value: "Out of stock", rgb: [244, 204, 204] }, // #f4cccc
{ value: "Pending", rgb: [252, 229, 205] }, // #fce5cd
];
const requests = colorObj.map(({ value, rgb }, i) => ({
addConditionalFormatRule: {
index: 0,
rule: {
booleanRule: {
condition: {
values: [
{
userEnteredValue: value,
},
],
type: "TEXT_EQ",
},
format: {
backgroundColor: {
red: rgb[0] / 255,
green: rgb[1] / 255,
blue: rgb[2] / 255,
},
},
},
ranges: [
{
sheetId,
startColumnIndex: 3,
endColumnIndex: 4,
startRowIndex: 1,
},
],
},
},
}));
const res = await googleSheets.spreadsheets.batchUpdate({
spreadsheetId,
resource: { requests },
});
- In this modification, this ConditionalFormatRule is reflected in the range of "D2:D".
Note:
- I guessed that from your showing script, you might be using googleapis for Node.js. So, this sample scirpt is for googleapis for Node.js.
References:
·
Juan Pablo Isaza
Denunciar
Responde la pregunta