Google Sheets can be a useful tool for collaboration between team members, but did you know that you can also add, edit and update content in a Google Sheet from your website or application? The Google Sheets API provides a variety of methods to interact with a sheet, and here we'll explore one of these methods step-by-step.
After configuring and enabling the Google Sheets API, you'll use the 'sheets.spreadsheets.values.append' method to publish a new row to a Google Sheet through an HTML form. To complete this project, you'll need a Google Account and a solid foundation in HTML, Javascript, and PHP.
Video Walkthrough of the Following Tasks:
1) Creating a New Project in the Google Developers Console
You will need to create a google account if you don't already have one. Sign in to Google and go to console.developers.google.com. From that dashboard is where you'll create a new project.
2) Enabling the Google Sheets API
Search for the 'Google Sheets API' and click to enable the new project you just created.
3) Configuring Credentials to Access the API
To add credentials to your project click on the create credentials link. In this example, you'll be making a client-side API call. In the dropdown of options make sure to select 'Web Browser (Javascript)'. Then for the type of data, select 'User Data'.
Since you can have multiple types of access, name the type of access you'll be granting, such as 'Append Data'. Then add your domain where you will be making API calls from. Under authorized redirect URIs add the page or pages that will handle your 0Auth login and token requests. If you are unsure at this time, you can always update this later.
4) Using the Google API Explorer to Create and Test a Sample Request
Go to your Google Drive and create a new Google Sheet. Name it and create the column headers, for this demo example you'll be creating three fields: First Name, Last Name, and Email.
Now you can test the API call by using the API explorer. Go to developers.google.com/apis-explorer. Find Google Sheets API and click to view the available methods. For this test you'll use sheets.spreadsheets.values.append. Include your 'spreadsheetid', you can take that from the URL of your spreadsheet.
When you've finished entering the corresponding values. You can test your request by toggling 'Authorize Request' and in the window, select the spreadsheet scope and click authorize. Scroll down to see the results.
5) OAuth Credentials - Obtaining an Authorization Code, Access Token, and Refresh Token (PHP)
You need a few identifiers for this project in order to make these API calls, so return to the developers console and go to credentials. Click on the 0Auth Client ID you created earlier.
Before making an API request, you need to grant access to the application to allow it to modify our sheet. Verify these permissions by supplying an access token each time a request is made. All of this is done using the Google 0Auth API.
In this example, you'll be using PHP. See the code attached in the resources ZIP download below.
6) Handling the HTML form data (Javascript)
With the JSON token response, we can move to the form submission. There are two files in the resources ZIP download below that contain the HTML form (tutorial-google-sheets-api.php) and the other is the Javascript (fdgs.js) to make the sheets API request.
7) Exchanging an OAuth Refresh Token for a refresh Access Token (Javascript)
You may find code examples of this request in the resource downloads below. For more information on access and refresh tokens, you may review the process in more detail on the OAuth website.
8) Calling the Google Sheets API method to post form data to the sheet (Javascript)
We then use curl to post the code to the token API URL. Putting together these last few steps together, using the resource javascript code and preformatted form, if all of the fields have values in them we can then proceed to the API call.
Resources
Download Sample Code
Find ZIP Folder with Code Samples Here
Improve Your Process
If you’re interested in leveraging Google's APIs in your marketing efforts and want to consult with the industry experts, contact Snyder Group Media.