10-12-2022, 02:48 AM
Google Sheets JavaScript API Spreadsheet Tutorial
<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2022/10/google-sheets-javascript-api-spreadsheet-tutorial.jpg" width="550" height="340" title="" alt="" /></div><div><div class="modified-on" readability="7.1304347826087"> by <a href="https://phppot.com/about/">Vincy</a>. Last modified on October 11th, 2022.</div>
<p>Google Sheets API provides services of to read and write a Google spreadsheet document.</p>
<p>This tutorial is for reading data from Google sheets and displaying them in the UI with JavaScript. Only JavaScript is used without any plugins or dependencies.</p>
<h2>Steps to access Google Sheets</h2>
<p>It requires the following steps to achieve this.</p>
<ol>
<li>Get OAuth Credentials and API keys and configure them into an application.</li>
<li>Authenticate and Authorise the app to allow accessing Google sheets.</li>
<li>Read spreadsheet data and store it in an array.</li>
<li>Parse response data and display them on the UI.</li>
</ol>
<p>Steps 1 and 2 are common for all Google JavaScript API services. When <a href="https://phppot.com/javascript/google-drive-upload-javascript/">uploading files to Google Drive via JavaScript API</a>, we have seen it.</p>
<p>We have also seen how to <a href="https://phppot.com/php/how-to-upload-files-to-google-drive-with-api-using-php/">upload to Google Drive using PHP</a>. It doesn’t need API Key. Instead, it does token-based authentication to get API access.</p>
<h2>Step 1: Get OAuth Credentials and API keys and configure them into an application</h2>
<p>In this step, it needs to create a developer’s web client app to get the client id and the API keys. For that, it requires the following setting should be enabled with the developer’s dashboard.</p>
<ol>
<li>Login to the Google Developers console and create a web client.</li>
<li>Enable Google Sheets API from the gallery of Google APIs.</li>
<li>Configure OAuth content screen to set the app details</li>
<li>Click OAuth credentials and get the app client id and secret key.</li>
<li>Set the scope on which the program going to access the spreadsheet.</li>
<li>Get the API key to authenticate and authorize the app to access the Google Spreadsheet API service.</li>
</ol>
<p><img loading="lazy" class="alignnone size-large wp-image-19712" src="https://phppot.com/wp-content/uploads/2022/10/enable-google-sheets-api-550x340.jpg" alt="enable google sheets api" width="550" height="340" srcset="https://phppot.com/wp-content/uploads/2022/10/enable-google-sheets-api-550x340.jpg 550w, https://phppot.com/wp-content/uploads/20...00x185.jpg 300w, https://phppot.com/wp-content/uploads/20...68x475.jpg 768w, https://phppot.com/wp-content/uploads/20...ts-api.jpg 1000w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>Note: The secret key will be used for server-side implementation, but not in this JavaScript example.</p>
<h3>Required scope to access the spreadsheet data</h3>
<p>The following scopes should be selected to read the Google Spreadsheets via a program.</p>
<ul>
<li><em>…auth/spreadsheets</em> – to read, edit, create and delete Spreadsheets.</li>
<li><em>…auth/spreadsheets.readonly</em> – to read Spreadsheets.</li>
<li><em>…auth/drive</em> – to read, edit, create and delete Drive files.</li>
<li><em>…auth/drive.readonly</em> – to read Drive files</li>
<li><em>…auth/drive.file</em> – to read, edit, create and delete a specific Drive files belongs to the app gets authorized.</li>
</ul>
<h2>Step 2: Authenticate and Authorise the app to allow accessing Google sheets</h2>
<p>Authorization is the process of the client signing into the Google API to access its services.</p>
<p>On clicking an “Authorize” button, it calls authorizeGoogleAccess() function created for this example.</p>
<p>This function shows a content screen for the end user to allow access. Then, it receives the access token in a callback handler defined in this function.</p>
<h2>Step 3: Read spreadsheet data and store it in an array</h2>
<p>Once access is permitted, the callback will invoke the script to access an existing Google spreadsheet.</p>
<p>The <em>listMajors()</em> function specifies a particular spreadsheet id to be accessed. This function uses JavaScript <em>gapi</em> instance to get the spreadsheet data.</p>
<h2>Step 4: Parse response data and display them on the UI</h2>
<p>After getting the response data from the API endpoint, this script parses the resultant object array.</p>
<p>It prepares the output HTML with the spreadsheet data and displays them to the target element.</p>
<p>If anything strange with the response, it shows the “No records found” message in the browser.</p>
<h2>A complete code: Accessing Google Spreadsheets via JavaScript</h2>
<p>The following script contains the HTML to show either “Authorize” or the two “Refresh” and “Signout” buttons. Those buttons’ display mode is based on the state of authorization to access the Google Spreadsheet API.</p>
<p>The example code includes the JavaScript library to make use of the required Google API services.</p>
<p>The JavaScript has the configuration to pin the API key and OAuth client id in a right place. This configuration is used to proceed with the steps 2, 3 and 4 we have seen above.</p>
<pre class="prettyprint"><code class="language-html"><!DOCTYPE html>
<html>
<head>
<title>Google Sheets JavaScript API Spreadsheet Tutorial</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>Google Sheets JavaScript API Spreadsheet Tutorial</h1> <p>This tutorial is to help you learn on how to read Google Sheets (spreadsheet) using JavaScript Google API.</p> <button id="authorize_btn" onclick="authorizeGoogleAccess()">Authorize Google Sheets Access</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>
</code></pre>
<pre class="prettyprint"><code class="language-javascript">// You should set your Google client ID and Google API key
const GOOGLE_CLIENT_ID = '';
const GOOGLE_API_KEY = '';
// const DISCOVERY_DOC = 'https://sheets.googleapis.com/$discovery/rest?version=v4'; // Authorization scope should be declared for spreadsheet handing
// multiple scope can he included separated by space
const SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'; let tokenClient;
let gapiInited = false;
let gisInited = false;
document.getElementById('authorize_btn').style.visibility = 'hidden';
document.getElementById('signout_btn').style.visibility = 'hidden'; /** * Callback after api.js is loaded. */
function gapiLoaded() { gapi.load('client', intializeGapiClient);
} /** * Callback after the Google API client is loaded. Loads the * discovery doc to initialize the API. */
async function intializeGapiClient() { await gapi.client.init({ apiKey: GOOGLE_API_KEY, discoveryDocs: [DISCOVERY_DOC], }); gapiInited = true; maybeEnableButtons();
} /** * Callback after Google Identity Services are loaded. */
function gisLoaded() { tokenClient = google.accounts.oauth2.initTokenClient({ client_id: GOOGLE_CLIENT_ID, scope: SCOPES, callback: '', // defined later }); gisInited = true; maybeEnableButtons();
} /** * Enables user interaction after all libraries are loaded. */
function maybeEnableButtons() { if (gapiInited && gisInited) { document.getElementById('authorize_btn').style.visibility = 'visible'; }
} /** * Sign in the user upon button click. */
function authorizeGoogleAccess() { tokenClient.callback = async (resp) => { if (resp.error !== undefined) { throw (resp); } document.getElementById('signout_btn').style.visibility = 'visible'; document.getElementById('authorize_btn').innerText = 'Refresh'; await listMajors(); }; if (gapi.client.getToken() === null) { // Prompt the user to select a Google Account and ask for consent to share their data // when establishing a new session. tokenClient.requestAccessToken({ prompt: 'consent' }); } else { // Skip display of account chooser and consent dialog for an existing session. tokenClient.requestAccessToken({ prompt: '' }); }
} /** * Sign out the user upon button click. */
function signoutGoogle() { const token = gapi.client.getToken(); if (token !== null) { google.accounts.oauth2.revoke(token.access_token); gapi.client.setToken(''); document.getElementById('content').innerText = ''; document.getElementById('authorize_btn').innerText = 'Authorize'; document.getElementById('signout_btn').style.visibility = 'hidden'; }
} /** * Print the names and majors of students in a sample spreadsheet: * https://docs.google.com/spreadsheets/d/1...Thvas/edit */
async function listMajors() { let response; try { // Fetch first 10 files response = await gapi.client.sheets.spreadsheets.values.get({ spreadsheetId: '', range: 'Sheet1!A2:D', }); } catch (err) { document.getElementById('content').innerText = err.message; return; } const range = response.result; if (!range || !range.values || range.values.length == 0) { document.getElementById('content').innerText = 'No values found.'; return; } const output = range.values.reduce( (str, row) => `${str}${row[0]}, ${row[2]}\n`, 'Birds, Insects:\n'); document.getElementById('content').innerText = output;
}
</code></pre>
<h2>Source and output of this example</h2>
<p>The spreadsheet shown in this screenshot is the source of this program to access its data.</p>
<p><img loading="lazy" class="alignnone size-large wp-image-19714" src="https://phppot.com/wp-content/uploads/2022/10/google-sheets-spreadsheet-source-550x501.jpg" alt="google sheets spreadsheet source" width="550" height="501" srcset="https://phppot.com/wp-content/uploads/2022/10/google-sheets-spreadsheet-source-550x501.jpg 550w, https://phppot.com/wp-content/uploads/20...00x273.jpg 300w, https://phppot.com/wp-content/uploads/20...source.jpg 600w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>The JavaScript example reads the spreadsheet and displays the Birds and the Insects column data in the UI.</p>
<p><img loading="lazy" class="alignnone size-large wp-image-19715" src="https://phppot.com/wp-content/uploads/2022/10/google-sheets-javascript-api-read-output-550x411.jpg" alt="google sheets javascript api read output" width="550" height="411" srcset="https://phppot.com/wp-content/uploads/2022/10/google-sheets-javascript-api-read-output-550x411.jpg 550w, https://phppot.com/wp-content/uploads/20...00x224.jpg 300w, https://phppot.com/wp-content/uploads/20...68x574.jpg 768w, https://phppot.com/wp-content/uploads/20...output.jpg 1000w" sizes="(max-width: 550px) 100vw, 550px"><br /><a class="download" href="https://phppot.com/downloads/javascript/google-sheets-javascript.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/javascript/google-sheets-javascript/#top" class="top">↑ Back to Top</a> </p>
</div>
https://www.sickgaming.net/blog/2022/10/...-tutorial/
<div style="margin: 5px 5% 10px 5%;"><img src="https://www.sickgaming.net/blog/wp-content/uploads/2022/10/google-sheets-javascript-api-spreadsheet-tutorial.jpg" width="550" height="340" title="" alt="" /></div><div><div class="modified-on" readability="7.1304347826087"> by <a href="https://phppot.com/about/">Vincy</a>. Last modified on October 11th, 2022.</div>
<p>Google Sheets API provides services of to read and write a Google spreadsheet document.</p>
<p>This tutorial is for reading data from Google sheets and displaying them in the UI with JavaScript. Only JavaScript is used without any plugins or dependencies.</p>
<h2>Steps to access Google Sheets</h2>
<p>It requires the following steps to achieve this.</p>
<ol>
<li>Get OAuth Credentials and API keys and configure them into an application.</li>
<li>Authenticate and Authorise the app to allow accessing Google sheets.</li>
<li>Read spreadsheet data and store it in an array.</li>
<li>Parse response data and display them on the UI.</li>
</ol>
<p>Steps 1 and 2 are common for all Google JavaScript API services. When <a href="https://phppot.com/javascript/google-drive-upload-javascript/">uploading files to Google Drive via JavaScript API</a>, we have seen it.</p>
<p>We have also seen how to <a href="https://phppot.com/php/how-to-upload-files-to-google-drive-with-api-using-php/">upload to Google Drive using PHP</a>. It doesn’t need API Key. Instead, it does token-based authentication to get API access.</p>
<h2>Step 1: Get OAuth Credentials and API keys and configure them into an application</h2>
<p>In this step, it needs to create a developer’s web client app to get the client id and the API keys. For that, it requires the following setting should be enabled with the developer’s dashboard.</p>
<ol>
<li>Login to the Google Developers console and create a web client.</li>
<li>Enable Google Sheets API from the gallery of Google APIs.</li>
<li>Configure OAuth content screen to set the app details</li>
<li>Click OAuth credentials and get the app client id and secret key.</li>
<li>Set the scope on which the program going to access the spreadsheet.</li>
<li>Get the API key to authenticate and authorize the app to access the Google Spreadsheet API service.</li>
</ol>
<p><img loading="lazy" class="alignnone size-large wp-image-19712" src="https://phppot.com/wp-content/uploads/2022/10/enable-google-sheets-api-550x340.jpg" alt="enable google sheets api" width="550" height="340" srcset="https://phppot.com/wp-content/uploads/2022/10/enable-google-sheets-api-550x340.jpg 550w, https://phppot.com/wp-content/uploads/20...00x185.jpg 300w, https://phppot.com/wp-content/uploads/20...68x475.jpg 768w, https://phppot.com/wp-content/uploads/20...ts-api.jpg 1000w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>Note: The secret key will be used for server-side implementation, but not in this JavaScript example.</p>
<h3>Required scope to access the spreadsheet data</h3>
<p>The following scopes should be selected to read the Google Spreadsheets via a program.</p>
<ul>
<li><em>…auth/spreadsheets</em> – to read, edit, create and delete Spreadsheets.</li>
<li><em>…auth/spreadsheets.readonly</em> – to read Spreadsheets.</li>
<li><em>…auth/drive</em> – to read, edit, create and delete Drive files.</li>
<li><em>…auth/drive.readonly</em> – to read Drive files</li>
<li><em>…auth/drive.file</em> – to read, edit, create and delete a specific Drive files belongs to the app gets authorized.</li>
</ul>
<h2>Step 2: Authenticate and Authorise the app to allow accessing Google sheets</h2>
<p>Authorization is the process of the client signing into the Google API to access its services.</p>
<p>On clicking an “Authorize” button, it calls authorizeGoogleAccess() function created for this example.</p>
<p>This function shows a content screen for the end user to allow access. Then, it receives the access token in a callback handler defined in this function.</p>
<h2>Step 3: Read spreadsheet data and store it in an array</h2>
<p>Once access is permitted, the callback will invoke the script to access an existing Google spreadsheet.</p>
<p>The <em>listMajors()</em> function specifies a particular spreadsheet id to be accessed. This function uses JavaScript <em>gapi</em> instance to get the spreadsheet data.</p>
<h2>Step 4: Parse response data and display them on the UI</h2>
<p>After getting the response data from the API endpoint, this script parses the resultant object array.</p>
<p>It prepares the output HTML with the spreadsheet data and displays them to the target element.</p>
<p>If anything strange with the response, it shows the “No records found” message in the browser.</p>
<h2>A complete code: Accessing Google Spreadsheets via JavaScript</h2>
<p>The following script contains the HTML to show either “Authorize” or the two “Refresh” and “Signout” buttons. Those buttons’ display mode is based on the state of authorization to access the Google Spreadsheet API.</p>
<p>The example code includes the JavaScript library to make use of the required Google API services.</p>
<p>The JavaScript has the configuration to pin the API key and OAuth client id in a right place. This configuration is used to proceed with the steps 2, 3 and 4 we have seen above.</p>
<pre class="prettyprint"><code class="language-html"><!DOCTYPE html>
<html>
<head>
<title>Google Sheets JavaScript API Spreadsheet Tutorial</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>Google Sheets JavaScript API Spreadsheet Tutorial</h1> <p>This tutorial is to help you learn on how to read Google Sheets (spreadsheet) using JavaScript Google API.</p> <button id="authorize_btn" onclick="authorizeGoogleAccess()">Authorize Google Sheets Access</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>
</code></pre>
<pre class="prettyprint"><code class="language-javascript">// You should set your Google client ID and Google API key
const GOOGLE_CLIENT_ID = '';
const GOOGLE_API_KEY = '';
// const DISCOVERY_DOC = 'https://sheets.googleapis.com/$discovery/rest?version=v4'; // Authorization scope should be declared for spreadsheet handing
// multiple scope can he included separated by space
const SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'; let tokenClient;
let gapiInited = false;
let gisInited = false;
document.getElementById('authorize_btn').style.visibility = 'hidden';
document.getElementById('signout_btn').style.visibility = 'hidden'; /** * Callback after api.js is loaded. */
function gapiLoaded() { gapi.load('client', intializeGapiClient);
} /** * Callback after the Google API client is loaded. Loads the * discovery doc to initialize the API. */
async function intializeGapiClient() { await gapi.client.init({ apiKey: GOOGLE_API_KEY, discoveryDocs: [DISCOVERY_DOC], }); gapiInited = true; maybeEnableButtons();
} /** * Callback after Google Identity Services are loaded. */
function gisLoaded() { tokenClient = google.accounts.oauth2.initTokenClient({ client_id: GOOGLE_CLIENT_ID, scope: SCOPES, callback: '', // defined later }); gisInited = true; maybeEnableButtons();
} /** * Enables user interaction after all libraries are loaded. */
function maybeEnableButtons() { if (gapiInited && gisInited) { document.getElementById('authorize_btn').style.visibility = 'visible'; }
} /** * Sign in the user upon button click. */
function authorizeGoogleAccess() { tokenClient.callback = async (resp) => { if (resp.error !== undefined) { throw (resp); } document.getElementById('signout_btn').style.visibility = 'visible'; document.getElementById('authorize_btn').innerText = 'Refresh'; await listMajors(); }; if (gapi.client.getToken() === null) { // Prompt the user to select a Google Account and ask for consent to share their data // when establishing a new session. tokenClient.requestAccessToken({ prompt: 'consent' }); } else { // Skip display of account chooser and consent dialog for an existing session. tokenClient.requestAccessToken({ prompt: '' }); }
} /** * Sign out the user upon button click. */
function signoutGoogle() { const token = gapi.client.getToken(); if (token !== null) { google.accounts.oauth2.revoke(token.access_token); gapi.client.setToken(''); document.getElementById('content').innerText = ''; document.getElementById('authorize_btn').innerText = 'Authorize'; document.getElementById('signout_btn').style.visibility = 'hidden'; }
} /** * Print the names and majors of students in a sample spreadsheet: * https://docs.google.com/spreadsheets/d/1...Thvas/edit */
async function listMajors() { let response; try { // Fetch first 10 files response = await gapi.client.sheets.spreadsheets.values.get({ spreadsheetId: '', range: 'Sheet1!A2:D', }); } catch (err) { document.getElementById('content').innerText = err.message; return; } const range = response.result; if (!range || !range.values || range.values.length == 0) { document.getElementById('content').innerText = 'No values found.'; return; } const output = range.values.reduce( (str, row) => `${str}${row[0]}, ${row[2]}\n`, 'Birds, Insects:\n'); document.getElementById('content').innerText = output;
}
</code></pre>
<h2>Source and output of this example</h2>
<p>The spreadsheet shown in this screenshot is the source of this program to access its data.</p>
<p><img loading="lazy" class="alignnone size-large wp-image-19714" src="https://phppot.com/wp-content/uploads/2022/10/google-sheets-spreadsheet-source-550x501.jpg" alt="google sheets spreadsheet source" width="550" height="501" srcset="https://phppot.com/wp-content/uploads/2022/10/google-sheets-spreadsheet-source-550x501.jpg 550w, https://phppot.com/wp-content/uploads/20...00x273.jpg 300w, https://phppot.com/wp-content/uploads/20...source.jpg 600w" sizes="(max-width: 550px) 100vw, 550px"></p>
<p>The JavaScript example reads the spreadsheet and displays the Birds and the Insects column data in the UI.</p>
<p><img loading="lazy" class="alignnone size-large wp-image-19715" src="https://phppot.com/wp-content/uploads/2022/10/google-sheets-javascript-api-read-output-550x411.jpg" alt="google sheets javascript api read output" width="550" height="411" srcset="https://phppot.com/wp-content/uploads/2022/10/google-sheets-javascript-api-read-output-550x411.jpg 550w, https://phppot.com/wp-content/uploads/20...00x224.jpg 300w, https://phppot.com/wp-content/uploads/20...68x574.jpg 768w, https://phppot.com/wp-content/uploads/20...output.jpg 1000w" sizes="(max-width: 550px) 100vw, 550px"><br /><a class="download" href="https://phppot.com/downloads/javascript/google-sheets-javascript.zip">Download</a></p>
<p> <!-- #comments --> </p>
<div class="related-articles">
<h2>Popular Articles</h2>
</p></div>
<p> <a href="https://phppot.com/javascript/google-sheets-javascript/#top" class="top">↑ Back to Top</a> </p>
</div>
https://www.sickgaming.net/blog/2022/10/...-tutorial/