1. Overview Show
In this codelab, you'll learn how you can use Google Sheets as a custom-reporting tool for your users. You'll modify a sample order tracking application to export to a spreadsheet and then build visualizations using the Google Sheets API. The sample application is built using Node.js and the Express web application framework, but the same basic principles are applicable to any architecture. What you'll learn
What you'll need
2. Get the sample codeYou can either download all the sample code to your computer... ...or clone the GitHub repository from the command line. git clone https://github.com/googleworkspace/sheets-api-codelab.git The repository contains a set of directories representing each step along the process, in case you need to reference a working version. You'll be working off the copy located in the 3. Run the sample appFirst, get the sample order-tracking application up and running. With the code downloaded, follow the instructions below to install and start the Node.js/Express web application:
npm install
npm start
The application provides the ability to create, update, and delete a set of simple order records. We've included a SQLite database with some sample data, but feel free to add, update, and delete orders as you progress through the codelab. Take a moment to familiarize yourself with the code, and refer to the table below for a general overview of the application's structure:
The rest of the codelab walks you through modifying the base application in the 4. Create a client IDBefore creating a client ID, you must turn on the Google Sheets API.
Next, add an OAuth client ID to your project.
5. Add Google Sign-inBefore you can start exporting data to Google Sheets, you need the user to sign in to your application with their Google Account and authorize access to their spreadsheets. To do this, we'll be using Google Sign-in for Websites, a JavaScript library you can add to an existing web app. The file views/layout.handlebars
Overwrite the placeholder This code sets the OAuth2 client ID, the requested scope, and includes the Google
Sign-in library. In this case, we're requesting the scope Next add the code that renders the sign-in button and displays the signed-in user's information. Add the following code to views/layout.handlebars
Finally, add some client-side JavaScript to populate the profile section once sign-in is complete. Add the following to public/script.js
Reload the application in your browser, click Sign in, and authorize access to your Google Account. Your name and email address should be displayed in the application's header. 6. Add spreadsheet controlsYou need to track the spreadsheets your application creates so if the data in the application changes, the spreadsheets can be updated. To do that, create a table in the database to store information about the spreadsheets. You'll also add some controls to the UI. Within the models/spreadsheets.js
This code uses the Sequelize ORM to define a new table that stores the ID, sheet ID, and name of the spreadsheets you create. Next, fetch the
spreadsheets you've stored when the index page loads, so you can display them in a list. In routes.js
Next, display the list of spreadsheets in the template. Add the following code to the end of views/index.handlebars
Finally, wire up the create spreadsheet and sync spreadsheet buttons. Add the following code to public/script.js
Reload the application in your browser. The new spreadsheets section should now be on the screen. Since the database is empty, there are no spreadsheets to show. As well, the create button won't do anything just yet. 7. Create spreadsheetsThe Google Sheets API provides the ability to create and update spreadsheets. To start using it, install the Google APIs Node.js client library and the companion authentication library. Run the following commands in your console (you might need to stop the server first): npm install googleapis@26.* --save npm install google-auth-library@1.* --save Next we'll create a helper class that will use the libraries to create and update our spreadsheets. Create a file called sheets.js
Given an OAuth2 access token, this class creates the credentials and initializes the Sheets API client. Next we'll add a method for creating a spreadsheet. Add the following to the end of sheets.js
This method defines a simple Finally, add a new route to our application that takes the request from the spreadsheet controls, calls the helper to create the spreadsheet, and then saves a record in the database. Add the following code to the end of routes.js
If you stopped your server above, restart the server using the following command: npm start In your browser, navigate to Click Create. A new spreadsheet is created and displayed in the list. Click the spreadsheet's name to open it, and you'll find it has one blank sheet named Data. 8. Add a header rowNow that we're creating spreadsheets, let's format them
starting with a header row. We'll have the application add this header row after it creates the spreadsheet. In sheets.js
This code uses the Sheets API's
Next we'll need to define the column headers. Add the following code to the end of sheets.js
This code above also defines the corresponding fields in the Order object (similar to database columns) which we'll use later on. Finally, define the
sheets.js
This code loops over each column and creates a Reload the application in your browser and click Create. The resulting spreadsheet should include a header row with a column for each defined field. 9. Synchronize data to the spreadsheetCreating and formatting spreadsheets is pointless if you don't add any actual data to it. First, let's add a new route to routes.js
Like the previous route for creating spreadsheets, this one checks for authorization, loads models from the database, and then passes the information to the sheets.js
Here again we're using the The sheets.js
The Reload the application in your browser and click Sync next to the spreadsheet link. The spreadsheet should now contain all your order data. Add a new order and click Sync again to view the changes. 10. Add a pivot table and chartYour application now exports to Google Sheets, but a similar result could have been achieved by exporting a CSV file and manually importing it into Google Sheets. What separates this API-based approach from CSVs is the ability to add complex features to spreadsheets, such as pivot tables and charts. This allows you to leverage Google Sheets as a dashboard to your data that users can customize and extend. To begin, we'll need to add a new sheet to our spreadsheet to contain the pivot table and chart. It's best to keep the sheet of raw data separate from any aggregations and visualizations so your syncing code can focus solely on the data. In sheets.js
Later on in the sheets.js
Finally, add the following functions to the file to create requests for building the pivot table, formatting the results, and adding the chart: sheets.js
Reload the application in your browser and click Create. The resulting spreadsheet should have a new sheet containing an empty pivot table and chart. Click Sync to add data to the spreadsheet. The pivot table and chart are also populated with data. 11. Congratulations!You've successfully modified an application to export data to Google Sheets. Users can now build custom reports and dashboards over your data without the need for any additional code, and all while being kept in sync as the data changes. Possible improvementsHere are some more ideas for making an even more compelling integration:
Learn more
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates. [{ "type": "thumb-down", "id": "missingTheInformationINeed", "label":"Missing the information I need" },{ "type": "thumb-down", "id": "tooComplicatedTooManySteps", "label":"Too complicated / too many steps" },{ "type": "thumb-down", "id": "outOfDate", "label":"Out of date" },{ "type": "thumb-down", "id": "samplesCodeIssue", "label":"Samples / code issue" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }] [{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }] How much does Google Sheet API cost?Their Standard plan starts at $499 and scales up depending on your ad spend. It gives you access to a vast collection of 500+ data sources and loads of great features to help you take better control of your marketing. The catch to Funnel is that you won't be working directly in Google Sheets.
Is Google Drive API free?All use of the Drive API is available at no additional cost.
Can I use Google Sheets as a API?The Google Sheets API lets you read, write, and format Google Sheets data with your preferred programming language, including Java, JavaScript, and Python.
How do I enable Google Sheets API?Enable an API. Go to the API Console.. From the projects list, select a project or create a new one.. If the APIs & services page isn't already open, open the console left side menu and select APIs & services, and then select Library.. Click the API you want to enable. ... . Click ENABLE.. |