Are you interested in learning how to read data from Google Sheets and display it in a user interface using JavaScript? Look no further! In this tutorial, we will walk you through the steps to access Google Sheets using JavaScript, without the need for any plugins or dependencies. It’s time to unlock the power of Google Sheets!
Why Use the Google Sheets JavaScript API?
The Google Sheets API provides a range of services for reading and writing Google Spreadsheet documents. By utilizing this API, you can seamlessly integrate Google Sheets with your web application or project. Whether you need to display dynamic data, fetch information from a shared spreadsheet, or even update the spreadsheet, the Google Sheets JavaScript API has got you covered.
Step 1: Obtaining OAuth Credentials and API Keys
To get started, you will need to follow these steps:
- Create a client web application in the Google Developer Console.
- Enable the Google Sheets API in the Google API gallery.
- Configure the OAuth consent screen to define your application’s details.
- Obtain the Client ID and Client Secret from the OAuth credentials.
- Set the appropriate scope for accessing the spreadsheet.
- Obtain the API key to authenticate and authorize your application to access the Google Spreadsheet API.
Note: The Client Secret will be used for server-side implementation and is not required for this JavaScript example.
Required Scope to Access Spreadsheet Data
To read Google Sheets via a program, you must select the following scopes:
- …auth/spreadsheets: Read, modify, create, and delete spreadsheets.
- …auth/spreadsheets.readonly: Read-only access to spreadsheets.
- …auth/drive: Read, modify, create, and delete Drive files.
- …auth/drive.readonly: Read-only access to Drive files.
- …auth/drive.file: Read, modify, create, and delete specific Drive files owned by the authorized application.
Step 2: Authenticating and Authorizing Your Application
Authorization is the process of connecting your client to the Google API to access its services. By clicking the “Authorize” button, the authorizeGoogleAccess() function in our example is called. This function displays a consent screen for the end user to authorize the access and receives the access token in a callback handler.
Step 3: Reading Spreadsheet Data and Storing it in an Array
Once authorization is granted, the callback invokes the script to access an existing Google Spreadsheet. The listMajors() function specifies a specific spreadsheet ID to access. Using the JavaScript instance of gapi, this function retrieves the data from the spreadsheet.
Step 4: Parsing the Response Data and Displaying it in the User Interface
After obtaining the API response data, our script processes the resulting array of objects. It prepares the output HTML with the spreadsheet data and displays it in the target element. If any abnormalities occur with the response, it shows a “No records found” message in the browser.
Complete Code: Accessing Google Sheets via JavaScript
Below is the complete code snippet that includes the HTML markup, the necessary JavaScript libraries, and the configuration to pin the API key and OAuth client ID in the appropriate places. This configuration is used to perform Steps 2, 3, and 4 that we discussed earlier:
<!DOCTYPE html>
<html>
<head>
<title>Tutorial: Google Sheets JavaScript API</title>
<link rel='stylesheet' href='style.css' type='text/css' />
<link rel='stylesheet' href='form.css' type='text/css' />
</head>
<body>
<div class="phppot-container">
<h1>Tutorial: Google Sheets JavaScript API</h1>
<p>In this tutorial, we'll show you how to read Google Sheets using the Google JavaScript API and display the data in a user-friendly manner.</p>
<button id="authorize_btn" onclick="authorizeGoogleAccess()">Authorize Access to Google Sheets</button>
<button id="signout_btn" onclick="signoutGoogle()">Sign Out</button>
<pre id="content"></pre>
</div>
<script async defer src="https://apis.google.com/js/api.js" onload="gapiLoaded()"></script>
<script async defer src="https://accounts.google.com/gsi/client" onload="gisLoaded()"></script>
</body>
</html>Source and Results of this Example
The spreadsheet shown in the screenshot below serves as the data source for this program to access:
The JavaScript example reads the spreadsheet and displays the data from the Birds and Insects columns in the user interface:
You can download the complete code example for accessing Google Sheets via JavaScript from Crawlan.com.
↑ Back to top
