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 create a spreadsheet and add data to it.
Prior Set-Up
As you'll recall from the previous articles, I created a GoogleDownload React component that was responsible for signing a user in to Google so that the application could have read/write access to their Google Sheets. That component included a button that, when clicked, would start the spreadsheet creation process: createGoogleSheet(). This is where we will start, and all of the logic described below will live in the sheets.js file.
Overall Process
Once inside the sheets.js file, our work will occur in 3 steps:
- Create a new, blank spreadsheet in the user's Google Drive
- Add data to the spreadsheet
- Add basic styling and conditional formatting to the spreadsheet (see next article).
1. Create a New Spreadsheet
To create a new spreadsheet, we will use the spreadsheets.create method provided by the Google Sheets API. This method allows us to define the title, number of sheets, and some basic sheet properties for our spreadsheet. To learn more about creating new spreadsheets, you can reference the documentation for creating spreadsheets here, and check out the code samples as well.
export function createGoogleSheet(data) {
var columns = [ "country", "cases", "todayCases", "deaths", "todayDeaths", "recovered", "todayRecovered", "active", "critical", "casesPerOneMillion", "deathsPerOneMillion", "tests", "testsPerOneMillion", "population", "activePerOneMillion", "recoveredPerOneMillion", "criticalPerOneMillion" ]
var request = {
properties: {
title: "WORLD COVID DATA"
},
sheets: [
{ //sheet1
properties: {
title: "Sheet1",
gridProperties: {
columnCount: columns.length,
rowCount: data.length + 1,
frozenRowCount: 1,
frozenColumnCount: 1,
},
tabColor: {
red: 1.0,
green: 0.3,
blue: 0.4
},
},
},
{ //sheet2
properties: {
title: "Sheet2",
gridProperties: {
columnCount: columns.length,
rowCount: data.length + 1,
frozenRowCount: 1,
frozenColumnCount: 1,
},
tabColor: {
red: 0.0,
green: 0.0,
blue: 1.0
},
},
},
],
}
window.gapi.client.sheets.spreadsheets.create(request)
.then((response) => {
addDataValues(response, data, columns)
const url = response.result.spreadsheetUrl
window.open(url, "_blank")
});
}
When this spreadsheet creation method is called when the download button is clicked, data to populate the spreadsheet is passed as an argument into the method. First, we define the variable columns to define the columns that will be included in the spreadsheet. These columns could be defined programmatically from the data passed in, hard-coded directly, or could be obtained by modifying the data object. In my case, I hard-coded the column names because my data was messy and I only wanted to include certain data points.
Next, we call the spreadsheets.create() method and pass in a request object and a callback function. Our request object includes all of the properties that will define our spreadsheet and the sheets within it. In this case, we are defining the spreadsheet title, we are defining two sheets within the spreadsheet, and setting the title, grid properties (column and row information), and tab color for each sheet.
When a response is received, it includes all of our spreadsheet information in the form of an object (response.result). we can trigger our next function to add data values to the currently blank spreadsheet, as well as call the window.open() function to open the Google Sheet in a new tab.
2. Add Data to the Spreadsheet
The addDataValues() function takes in the response object from the spreadsheet creation function, the data, and the columns array.
First, we need to define variables for the spreadsheet object and the spreadsheetId, both of which are pulled out of the response object.
Next, we have to work with our data and format it into a two-dimensional array in order to add it to the spreadsheet. The data should be transformed into the following format:
var values = [
[column1header, column2header, column3header],
[row2col1data, row2col2data, row2col3data],
[row3col1data, row3col2data, row3col3data],
// etc...
]
This can be done for each sheet individually. As you can see in the commented out code below, you could create a separate values array for valuesSheet1 and valuesSheet2.
Next, we create a data array, which includes the range and values for each "chunk" of data we are adding to the spreadsheet. In my example, I added a separate entry for sheet1 and sheet2.
Finally, we create a body object, which includes our data, as well as the valueInputOption value, which can be set to either "RAW" or "USER_ENTERED_VALUE".
Once the body object is defined, we are ready to call the spreadsheets.values.batchUpdate() method to update the spreadsheet with all of the data we have defined in the body object. After receiving a response, we can then call our final method, which will add styling and conditional formatting to the spreadsheet cells.
export function addDataValues(response, spreadsheetData, columns) {
var spreadsheet = response.result
var spreadsheetId = response.result.spreadsheetId;
var valuesSheet1 = [columns]
spreadsheetData.forEach(country => {
var row = []
columns.forEach(heading => {
row.push(country[heading])
})
valuesSheet1.push(row)
})
// var valuesSheet2 =
var data = [];
data.push(
{
range: "Sheet1",
values: valuesSheet1
},
// {
// range: "Sheet2",
// values: valuesSheet2
// }
);
var body = {
data: data,
valueInputOption: "USER_ENTERED"
};
window.gapi.client.sheets.spreadsheets.values.batchUpdate({
spreadsheetId: spreadsheetId,
resource: body
}).then(() => addFormatting(spreadsheet));
}
To learn more about adding data values, check out the documentation on writing cell values and code samples for basic writing.
Almost done! Check out the next article in the series to see how to add basic and conditional formatting to the spreadsheet through the addFormatting(spreadsheet) method.
Top comments (1)
Thank you