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.
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
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.
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.
Download Sample Code
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.