If you've just landed here, make sure you check out the previous articles to see how I wired up an old React application with Google Sign-in, so that it could generate new Google Sheets with the click of a button. In this article, I'll describe how I used vanilla JavaScript and the Google Sheets API to add basic formatting as well as conditional formatting to the cells of the spreadsheet.
Formatting Goals
For this project, I had two main goals for formatting. My first goal was to add some basic formatting that would be applied to cells basic on their position (for example, bolding the header rows and first column). My second goal was to add some conditional formatting - that is, formatting that would be applied to specific cells based on the value of that cell (or another cell, for that matter). After some research, I learned that I could apply both the basic formatting and conditional formatting within one function.
Setting Up the addFormatting Function
Here is the basic skeleton of the function to accomplish both types of formatting. In the next sections, I'll break down the request object for each type of formatting below.
export function addFormatting(spreadsheet) {
var spreadsheetId = spreadsheet.spreadsheetId;
var sheet1Id = spreadsheet.sheets[0].properties.sheetId
var sheet2Id = spreadsheet.sheets[1].properties.sheetId
var requests = [
// add an object for each basic formatting rule
//add an object for each conditional formatting rule
];
var body = {requests: requests}
window.gapi.client.sheets.spreadsheets.batchUpdate({
spreadsheetId: spreadsheetId,
resource: body
}).then((response) => {
console.log(`formatting and conditional formatting updated.`);
});
}
In the addFormatting function, we start by defining some variables by pulling values out of the spreadsheet object that was passed into the function (spreadsheetId, sheet1Id, and sheet2Id).
Next, we will create an array of request objects that define all of the properties of the formatting we would like to update. The next two sections of the article will describe this in detail, but for now, we know that this will be an array of objects.
Once we have a requests array, we can define the body of our request by setting up an object with a key of "requests" pointing to the value of our requests array.
Finally, we are ready to make a call to the Google Sheets API using the spreadsheets.batchUpdate() method. Our argument is an object containing the spreadsheetId of the spreadsheet we are updating, as well as the body of our request. When a response is received, we can then call another function, or for now, just print a message to the console confirming that the formatting has been updated.
Below, I'll focus on how to create the request objects for updating basic formatting, as well as simple conditional formatting.
Basic Formatting Requests
In our skeleton addFormatting function above, we set up an empty requests array. To add basic formatting, we simply need to add an object that defines the properties of each style that we want to update. For example, the code below includes two request objects: One to make the header row text bold, and one to make the first column text bold.
var requests = [
// BOLD TEXT IN HEADER ROW
{
repeatCell: {
range: {
sheetId: sheet1Id,
startRowIndex: 0,
endRowIndex: 1
},
cell: {
userEnteredFormat: {
textFormat: {
bold: true
}
}
},
fields: "userEnteredFormat.textFormat.bold"
}
},
// BOLD TEXT IN FIRST COLUMN
{
repeatCell: {
range: {
sheetId: sheet1Id,
startColumnIndex: 0,
endColumnIndex: 1
},
cell: {
userEnteredFormat: {
textFormat: {
bold: true
}
}
},
fields: "userEnteredFormat.textFormat.bold"
}
},
// ADD ADDITIONAL REQUEST OBJECTS HERE
];
Let's break down the first request, to make the header row bold. First, we define the repeatCell property, which lets us set the range that the formatting will be applied to, which often includes the sheetId, startRowIndex, endRowIndex, startColumnIndex, and endRowIndex. The rows and columns are zero-indexed. In this example, we can leave out the column indices to apply the formatting across the entire row.
Next, we define the cell property, to define exactly what change we want to make to each cell that was defined in the repeatCell property. Finally, we need to define a fields property, which basically constrains the update to only the fields that we identify. Since we are only changing the text style in this request, we can set the field to fields: "userEnteredFormat.textFormat.bold".
You can learn more about basic formatting and check out some sample code on the Basic Formatting page of the Google Sheets API documentation. There is also a short video to help you get started.
Conditional Formatting Requests
Creating a conditional formatting request is fairly similar to creating a basic formatting request, but we will use a few different properties. Here is an example of two requests that will change the background color of a cell based upon the value within the cell:
var requests = [
//SET BACKGROUND COLOR TO GREEN IF VALUE <= 999,999
{
addConditionalFormatRule: {
rule: {
ranges: [ {
sheetId: sheet1Id,
startRowIndex: 1,
endRowIndex: 221,
startColumnIndex: 1,
},
// add another range object if needed
],
booleanRule: {
condition: {
type: 'NUMBER_LESS_THAN_EQ',
values: [ { userEnteredValue: "999999" } ]
},
format: {
backgroundColor: { green: 1.0 }
}
}
},
index: 0
}
},
//SET BACKGROUND COLOR TO RED IF VALUE > 1,000,000
{
addConditionalFormatRule: {
rule: {
ranges: [ {
sheetId: sheet1Id,
startRowIndex: 1,
endRowIndex: 220,
startColumnIndex: 1,
} ],
booleanRule: {
condition: {
type: 'NUMBER_GREATER_THAN_EQ',
values: [ { userEnteredValue: "1000000" } ]
},
format: {
backgroundColor: { red: 1.0 }
}
}
},
index: 1
}
},
// ADD MORE CONDITIONAL FORMATTING REQUESTS HERE
];
Let's break down the first request. Each request starts with the property addConditionalFormatRule, and then explicitly defines the rule that will be applied. Similar to the basic formatting examples above, we can set the ranges to which the rule will be applied, in the form of an array including an object to define each range.
Next, we can define either a booleanRule (formatting applied based on whether a condition is true or false) or a gradientRule (formatting applied across a gradient, such as changing the color shade based on the value). You can learn more about the two types of rules here.
For our booleanRule, we need to set the condition that will be evaluated by setting the type (for example, NUMBER_LESS_THAN_EQ, and the values that will be checked. In the first example, values: [ { userEnteredValue: "999999" } ], indicates that within the identified range, any cell with a value that is less than or equal to 999,999 would have the formatting applied to it. The format is defined next: backgroundColor: { green: 1.0 }.
The last part of a conditional formatting request is the index, which defines the order in which each conditional formatting request would be applied. Again, indexing starts at zero.
The code above is just a simple example that shows how to apply background color based on the values within cells, but there is a lot more you can do using custom formulas, similar to what you can do working within a google sheet in your browser. To learn more, check out the Documentation for Conditional Formatting and the Code Samples for Conditional Formatting.
Top comments (1)
Too cool, Jesse! You are on a roll! It is AWESOME to see your progress!