How to Get Data Using Google Sheets API (Complete Guide)

Key Takeaways: How to Get Data Using Google Sheets API

To Get Data Using Google Sheets API

Visit https://console.cloud.google.com/
Select an existing project or create a new one.
Name your project and click “Create“.
Navigate to the main menu, select “Marketplace,” search for “Sheets API,” and enable it.
Go to “Credentials,” and create credentials.
Set up API keys, OAuth consent screen, scope, and test users as needed.
Go to “spreadsheets.values” and select “get.“
Enter the spreadsheet ID, range, and other optional parameters. Rename the Google Sheets file if needed.

In today’s article, we will learn how to pull cell value from google sheets API. If you don’t know what is google sheets API, and if you don’t have any idea about pulling cell values from google sheets, then don’t worry this article will teach you beginning to advance about how to pull cell value from google sheets API.

Table of Contents

What is Google Sheets API?

First, let’s understand what is an API. API is an application programming interface that is developed to interchange the data from two or more unique applications or systems. API helps to get or post data to a third-party web application without accessing sensitive information. APIs are very common today and we have millions of APIs created for different purposes. Today we will talk about google sheets API. Google Sheets API is an amazing platform for google sheets users that help us to get or post data into google sheets. It is offered by the google cloud platform and it’s very easy to use.

You must have a google sheet API project created to use this API, using this API you can easily manipulate your google sheets with the help of google sheet API. Let’s set up us google sheet API to learn how to pull cell value from google sheets API.

Download/Copy Practice Workbook

Google Sheets API: Get Cell Value using getMethod

Google Sheets API: Get Value as String using getAsString

How to Set up Google Sheets API

Step 1

Go to https://console.cloud.google.com/

Step 2

Select a project or create a new project

Step 3

Name your project and click create.

Step 4

Go to the main menu > marketplace > search sheet API and hit enter

Step 5

Open Google sheets API and click on enable

Step 6

Go to credentials > create credentials

Step 7

Setup API keys

Setup OAuth consent screen

Setup Scope

Add or remove scope > select all > click save and continue

Setup Test users

Don’t add any users, click on back to dashboard

Click on try this API

Go to spreadsheets.values > get

Insert spreadsheetId, range, and other optional parameters.

Rename the google sheets file. For example, I have used “test” here.

Click on execute to get the data from your google sheet file.

How to Get Data from Google Sheets API

Getting data from a spreadsheet from API is no longer a tough job if you know the fundamentals of programming. It’s all about making your functions and connecting with Google sheet API and you can do almost anything programmatically using JavaScript, Python, PHP, and many more programming languages.

Most of us don’t know code and we don’t want to get stuck for this reason. This article is to provide easy tricks and guides to quickly get the result without any prior knowledge of coding; you can get the data from API without getting overwhelmed.

Step 1

Go to console.clound.google.com

Step 2

Go to spreadsheets.values > get

Step 3

Enter sheetId and range to get the data from the spreadsheet using google sheet API. Simple and easy as you like.

Google Sheets API to Find Row by Value

Nothing here.

How to use Google Apps Script to Get Cell Value

Google apps script call values allow you to get the values throughout the google sheet by cell address, or by any means (can be defined in code). We can use it to get the values without an API, it works with the code within the apps script and returns the value on the spreadsheet file.

To begin with Google apps script cell value, firstly you need to have a code that could be written in any language, most preferably it is in JavaScript. You can copy the code to use for free.

Source code to get cell value using Google Sheets API

function onOpen() 
{
var ui = SpreadsheetApp.getUi();
ui.createMenu('GetValues')
.addItem('get current', 'getCurrentCellValue')
.addItem('get by row col', 'getByRowAndColumn')
.addItem('get by address a1', 'getByCellAddressA1Notation')
.addToUi();
}

function getCurrentCellValue()
{
var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
var a1 = cell.getA1Notation();
var val = cell.getValue();

SpreadsheetApp.getUi().alert("The active cell "+a1+" value is "+val);
}

function getByRowAndColumn()
{
var ui = SpreadsheetApp.getUi();

var response = ui.prompt(
'Cell address',
'Please enter the cell address in this format: row,col for example, 5,2 means row 5 col 2',
ui.ButtonSet.OK);

var resp_text = response.getResponseText();

var match = resp_text.match(/^(\d+)\,(\d+)/);


var row = parseInt(match[1]);
var col = parseInt(match[2]);
var value = SpreadsheetApp.getActiveSheet().getRange(row,col).getValue();


SpreadsheetApp.getUi().alert("The value is "+value);
}

function getByCellAddressA1Notation()
{
var ui = SpreadsheetApp.getUi();

var response = ui.prompt(
'Cell address',
'Please enter the cell address (A1 notation)',
ui.ButtonSet.OK);

var a1 = response.getResponseText();

var value = SpreadsheetApp.getActiveSheet().getRange(a1).getValue();

ui.alert("cell value "+value);
}

you can also get the full spreadsheet (the apps script code along with the spreadsheet will be copied)

Here is the spreadsheet

https://docs.google.com/spreadsheets/d/1uLBTlE_-u0PlWDzzLsyPZ5uvytWpNh0oPt5owPWLOPA/copy

These functions are written in JavaScript, they are simply search and return algorithms. We can use them for complex data sets, as its OOP supported, google sheets is cell-oriented, similarly we can use cell orientation to use these functions for complex data sheets even correspondingly on multiple sheets within a google spreadsheet file.

It is safe and easy, go and play around to get familiar with it.

Google Sheets Apps Script to Set Cell Value

You can set values using the google apps script set cell value. This can be done using a small piece of code that is functional and it will set cell values as per cell addresses pointers. Let’s see how it works

Here is the code:

function sheetApp() {
// set value to any cell
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet2").getRange('E4').setValue("inserted value")
}

It takes call value in A1 notation and a string to pass in that cell.

Code is very simple; you can easily play around and customize it according to your requirements.

Practice the code on your own and you will love it.

This function is very simple, it uses the basic principles of mutation of the data, it works like an array and replace the data by the new data. Similarly, in JavaScript or PHP, if a block scope variable is defined twice then it takes the second value and overwrite the initial value. This function is taking cell address and a string and replace the old value of that cell address, remember if there is no old value, then it will replace the empty space with the new value.

If it is confusing to you? You can leave it, its just for logical definition of the function. Although if you want to add a numeric value, or any other data type that is not string, you can add it using the same method. This code reads the value as string but it will written you any data type that you specify.

Google Sheets Get Cell Value as String

We can use the same get method for google sheets get cell value as string, but in addition, we use a simple concat method to concat our cell data with some text (string) and it makes all the values equal to a string. This is so simple.

=concat(“anystring ” , cellAddress)

Google Sheets API Get Sheet Id

Google sheet Id is an important attribute of any google sheet or spreadsheet. It is a unique value that identifies any google sheet on the internet. We can find the id of any google sheet file from its URL.

Example

https://docs.google.com/spreadsheets/d/1g6GeX6NaKh-sIkUFbYoDqyvuklrn3mfcS–nTnB7r-M/copy

In the above URL, the blue highlighted content is the unique id. For easiness, you can refer to that after /d/ and before /edit content is the id of the spreadsheet.

To recap, we learned how to pull cell values from google sheets API, we have discussed the very basic concept of creating and setting up the first google sheet API to get started. Then we discussed how to get a specific value and how to set a specific cell value. We used Google sheets API and Google Apps Script to achieve all these. It’s very simple, code snippets are available for everyone and the google sheets are also free to use for everyone. Then we discussed how to get your spreadsheet ID. It is very straightforward forward you can get the spreadsheet ID from the URL. Finally, we saw how to get a cell value as a string, this method doesn’t use any script or code. It is a simple formula-based trick to concat an empty string with the original value.

Tutorial: Google Sheets API

Notes

To get value from google sheets API you need to set up your google sheet API by following the steps described above with screenshots.
You can python or any other language to do it, but if you are not familiar with coding you can easily use the Google API which provides an easy UI to perform this task.
Getting cell values is very easy you can use the code provided to use the full functionality of getting call values.
Similarly, you can set cell values by using the small code of the google apps script. Remember that all these methods are mutable and they will affect your original data.
Getting call values as string is not a commonly used feature but very helpful when working on website data, you need to convert an input into a string before any operation is performed, here you can use string concatenation, it is a fast and powerful feature that converts any data type into a string.
To get the google sheet id you can easily copy the link from the spreadsheet URL, In the above URL, the blue highlighted content is the unique id. For easiness, you can refer to that after /d/ and before /edit content is the id of the spreadsheet.

Conclusion

In this article, we learned how to pull cell value from google sheets API. Now google sheets API is a vast topic to cover in one article, so we learned a few techniques and their implementation using google apps script, and google sheets API. After this article, you can easily set up your google sheet API on your own, and you can use it by connecting it with your google cloud console account.

In the beginning, we taught you all the steps to set up google sheets API, then we discussed how to pull cell values from google sheet API, then we discussed several techniques, we saw how to get data from google sheet API, google app script get cell value, and get value, we also saw google sheets get call value as a string, and in last we discussed spreadsheet id, how to get the id of the spreadsheet from the URL, it’s very simple. I described it two times and simplified it for your ease.

Thank you for giving a read to this article, if you like this article, please share it on your social networks and subscribe OfficeDemy blog to get future updates.