Connecting Google Sheets to REST API Integration

Google Sheets helps make your data pop with colorful graphs and charts. With features like built-in formulas, conditional formatting options, and pivot tables, you can save time and simplify common spreadsheet tasks for free. It also allows you to get a headstart with a vast variety of schedules, budgets, and other pre-built spreadsheets – all designed to make your work all the better while making your life much easier.

In this article, you will gain information about Google Sheets REST API Integration. You will also gain a holistic understanding of Google Sheets, its key features, Google Sheets automation, and the steps involved in migrating data from API to Google Sheets . Read along to find out in-depth information about how to connect API to Google Sheets.

Introduction to Google Sheets

Google Sheets is a free Web-based Spreadsheet Program provided by Google as a part of its Google Apps Suite. It allows multiple users to create, edit and collaborate on spreadsheets in real time.

Google Sheets is a free and fully functional Spreadsheet program that is compatible with the most popular Spreadsheet formats. Since Google Sheets is a Cloud-based Software-as-a-Service (SaaS) utility, your files are accessible from anywhere via computer and mobile devices.

Key Features of Google Sheets

The key features of Google Sheets are as follows:

• Collaborative Editing: One of the most widely used features of Google Sheets is collaborative editing in real-time. This allows multiple people to work on a single sheet from different devices at any point in time. Google Sheets also houses a robust sidebar chat feature that gives collaborators the ability to discuss edits in real time and make recommendations either through chat or its comment functionality. Collaborators can also choose to track changes using the revision history feature.
• Offline Editing: Google Sheets allows users to edit files even if they are not connected to the internet. On the desktop, users can install the Google Docs offline extension on Google Chrome to enable offline editing for all applications within Google Docs.
• Integration with Google products: Google Sheets and other Google Docs applications can easily be integrated with other Google products such as Google Forms, Google Translate, Google Finance, etc.

To streamline your workflow and learn how to sync Excel to Google Sheets in just 3 easy steps do check out our blog post on how to sync Excel to Google Sheets.

Additionally, discover the ins and outs of using SQL in Google Sheets to manipulate and analyze your data effectively, empowering you to make data-driven decisions with confidence with our guide on Google Sheets SQL: A Complete Guide.

Introduction to REST API

REST API stands for Representational State Transfer, which presents some constraints of the REST architecture style. An HTTP request is used to access and use the data with commands such as PUT, GET, DELETE, POST, etc. 

A REST API, basically defines how different applications communicate over HTTP.  These different components remain loosely coupled and the transfer of information is fast and carried out efficiently. Since data formats aren’t defined, it serves a more diverse purpose and is easier to develop. Thus, REST APIs are a popular choice among web developers and have emerged as the standard protocol for web-based APIs.

The REST architecture can be understood as follows:

Understanding REST Components

A REST message contains these components:

• Resource Path
• HTTP Verb
• Body
• Header

A) Resource Path (Request Target)

Resource Path is the URL of the resource/object to be acted upon. The resource ID must be included in the path. The following resource path, for example, identifies a specific transaction/resource in the database.

https://apitest.abcde.com/pts/v3/payments/

The main part of the resource path begins after the host, apitest.abcde.com. “/pts/v2/payments” is the address of the Resource on the abcde.com end that processes transaction detail requests. abcde.com returns a request ID that provides transaction details that can be used for follow-up transactions, queries, or references.

B) HTTP Verb

The HTTP verbs express the action to be taken with respect to the resource:

• POST: Creates a resource
• GET: Retrieves one or more resources
• PUT: Updates a resource
• DELETE: Deletes a resource

These verbs are known as CRUD operations. They enable Resource Creation, Retrieval, Updating, and Deletion.

B) Body

A POST or PUT request has a “Body”. A GET request doesn’t have a “Body”. REST’s content format leverages JSON (JavaScript Object Notation). You can generate sample JSON request messages to help you structure your requests.

C) Header

The Header is a collection of fields and their associated values that provide the receiver with information about the message. It can be considered as metadata about the message. The header also includes authentication information, which informs the domain that the message is genuine.

Understanding Google Sheets REST API Integration

Google Sheet REST API are the Application Programming Interfaces (APIs) that are used to read and write data in Google Sheets with respect to the data sent or received from other Applications.

Service Endpoint

A Service Endpoint is a base URL that specifies an API service’s network address. There could be multiple service endpoints for a single service. This service has the following service endpoint, and all of the URIs (REST Resources) are relative to this service endpoint.

https://sheets.googleapis.com

Some of the Rest Resources are as follows:

• REST RESOURCE: Spreadsheets
• REST RESOURCE: Developer MetaData
• REST RESOURCE: Sheets
• REST RESOURCE: Values

A) REST Resource: Spreadsheets

Method	Functions
batchUpdate	POST /v4/spreadsheets/{spreadsheetId}:batchUpdate Applies one or more updates to the spreadsheet.
create	POST /v4/spreadsheets Creates a spreadsheet, returning the newly created spreadsheet.
get	GET /v4/spreadsheets/{spreadsheetId} Returns the spreadsheet at the given ID.
getByDataFilter	POST /v4/spreadsheets/{spreadsheetId}:getByDataFilter Returns the spreadsheet at the given ID.

For further information on Spreadsheets REST Resource.

B) Resource: Developer MetaData

Methods	Function
get	GET /v4/spreadsheets/{spreadsheetId}/developerMetadata/{metadataId}Returns the developer metadata with the specified ID.
search	POST /v4/spreadsheets/{spreadsheetId}/developerMetadata:search Returns all developer metadata matching the specified DataFilter.

Fur further information on Developer MetaData Resource.

C) Resource: Sheets

Methods	Function
copyTo	POST /v4/spreadsheets/{spreadsheetId}/sheets/{sheetId}:copyTo Copies a single sheet from a spreadsheet to another spreadsheet.

For further information on Sheets Resource.

D) Resource: Values

Methods
append	POST /v4/spreadsheets/{spreadsheetId}/values/{range}:append Appends values to a spreadsheet.
batchClear	POST /v4/spreadsheets/{spreadsheetId}/values:batchClear Clears one or more ranges of values from a spreadsheet.
batchClearByDataFilter	POST /v4/spreadsheets/{spreadsheetId}/values:batchClearByDataFilter Clears one or more ranges of values from a spreadsheet.
batchGet	GET /v4/spreadsheets/{spreadsheetId}/values:batchGet Returns one or more ranges of values from a spreadsheet.
batchGetByDataFilter	POST /v4/spreadsheets/{spreadsheetId}/values:batchGetByDataFilter Returns one or more ranges of values that match the specified data filters.
batchUpdate	POST /v4/spreadsheets/{spreadsheetId}/values:batchUpdate Sets values in one or more ranges of a spreadsheet.
batchUpdateByDataFilter	POST /v4/spreadsheets/{spreadsheetId}/values:batchUpdateByDataFilter Sets values in one or more ranges of a spreadsheet.
clear	POST /v4/spreadsheets/{spreadsheetId}/values/{range}:clear Clears values from a spreadsheet.
get	GET /v4/spreadsheets/{spreadsheetId}/values/{range} Returns a range of values from a spreadsheet.
update	PUT /v4/spreadsheets/{spreadsheetId}/values/{range} Sets values in a range of a spreadsheet.

For further information on values resource.

You can follow the official documentation for further information on Google Sheets REST API Integration.

Implementing Google Sheets REST API Integration

Google Apps Script is a Javascript-based scripting language, hosted and run on Google servers, that extends the functionality of Google Apps. Using Google Apps Script, we can connect a Google Sheet to a REST API and retrieve data from that API (for example, Numbers API) into our Google Sheet.

Establishing Google Sheets REST API Integration can be illustrated with an example of calling a REST API called Numbers API from Google Sheets.

The steps involved in connecting Google Sheets REST API Integration (Numbers API) are as follows:

• Open a new Sheet
• Open Apps Script Editor
• Name the Project
• Add API Example Code
• Run the Function
• Authorize your Script
• View the Logs

Step 1: Open a new Sheet

• Open a new blank Google Sheet and name it as per your requirement.

Step 2: Open Apps Script Editor

• Select the “Tools” option in the Menu bar and then select “Script Editor“.
• Script Editor Window appears in a new tab.

Step 3: Name the Project

• Name the project as per your requirement.

Step 4: Add API example code

• In the script editor tab, you can write your piece of code. So, remove all the code that is currently in the Code.gs file, and replace it with the following piece of code:

function callNumbers() {
  
  // Call the Numbers API for random math fact
  var response = UrlFetchApp.fetch("http://numbersapi.com/random/math");
  Logger.log(response.getContentText());
  
}

In this case, UrlFetchApp class is leveraged to communicate with other applications on the internet to access resources, to fetch a URL.

Step 5: Run the Function

• Now, to run the function click the “play” button in the toolbar.

Step 6: Authorize your Script

• All pop-up appears that will prompt you to authorize your script to connect to an external service.
• Click the “Review Permissions” button.
• Now click on the “Allow” button to continue the process..

Step 7: View the Logs

• After clicking the “Allow” button, the program will run successfully. It will send a request to a third party for the data as mentioned in the code i.e, a random math fact and that service will respond with that data.
• Logger.log() used in the code helps in recording the response text in the log files.
• To view the response, select the “View” option in the Menu bar.
• Now select the “Logs” option.

You’ll see an output i.e., a random math fact such as:

[17-02-03 08:52:41:236 PST] 1158 is the maximum number of pieces a torus can be cut into with 18 cuts.

You can also try with different URLs such as:

• http://numbersapi.com/random/trivia
• http://numbersapi.com/4/17/date
• http://numbersapi.com/1729

Additional Resources on Google Sheets Rest API Integration

• Explore more on Sync Excel to Google Sheets

Conclusion

In this article, you have learned about Google Sheets REST API Integration. This article also provided information on Google Sheets, its key features, REST API, Components of REST API, and steps involved in implementing Google Sheets REST API Integration.

Frequently Asked Questions