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

How to pull cell value from google sheets api 21

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.

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.

See also  How to Change Text Color Using Conditional Formatting in Google Sheets

Download/Copy Practice Workbook

How to Set up Google Sheets API

Step 1

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

How to pull cell value from google sheets api 1

Step 2

Select a project or create a new project

How to pull cell value from google sheets api 2

How to pull cell value from google sheets api 3

Step 3

Name your project and click create.

How to pull cell value from google sheets api 4

Step 4

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

How to pull cell value from google sheets api 5

How to pull cell value from google sheets api 6

Step 5

Open Google sheets API and click on enable

How to pull cell value from google sheets api 7

How to pull cell value from google sheets api 8

Step 6

Go to credentials > create credentials

How to pull cell value from google sheets api 9

How to pull cell value from google sheets api 10

How to pull cell value from google sheets api 11

Step 7

Setup API keys

How to pull cell value from google sheets api 12

How to pull cell value from google sheets api 13

Setup OAuth consent screen

How to pull cell value from google sheets api 14

How to pull cell value from google sheets api 15

How to pull cell value from google sheets api 16

Setup Scope

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

How to pull cell value from google sheets api 17

How to pull cell value from google sheets api 18

How to pull cell value from google sheets api 19

Setup Test users

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

How to pull cell value from google sheets api 20

Click on try this API

How to pull cell value from google sheets api 21

Go to spreadsheets.values > get

How to pull cell value from google sheets api 22

How to pull cell value from google sheets api 23

Insert spreadsheetId, range, and other optional parameters.

How to pull cell value from google sheets api 24

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

How to pull cell value from google sheets api 25

How to pull cell value from google sheets api 25

How to pull cell value from google sheets api 27

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

How to pull cell value from google sheets api 28

How to pull cell value from google sheets api 29

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

How to pull cell value from google sheets api 1

Step 2

Go to spreadsheets.values > get

How to pull cell value from google sheets api 22

 

How to pull cell value from google sheets api 23

Step 3

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

How to pull cell value from google sheets api 28

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.

How to pull cell value from google sheets api 30

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.

How to pull cell value from google sheets api 31

How to pull cell value from google sheets api 32

How to pull cell value from google sheets api 33

How to pull cell value from google sheets api 34

How to pull cell value from google sheets api 35

How to pull cell value from google sheets api 36

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

How to pull cell value from google sheets api 37

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.

How to pull cell value from google sheets api 38

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.

How to pull cell value from google sheets api 39

How to pull cell value from google sheets api 40

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.

See also  How to Insert Check Mark in Google Sheets [3 Methods]

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)

How to pull cell value from google sheets api 41

How to pull cell value from google sheets api 42

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.

How to pull cell value from google sheets api 43

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.
See also  How to Rotate Text in Google Sheets [Complete Guide]

Tutorial: Google Sheets API

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.

See also  How to Delete Empty Rows in Google Sheets [4 Methods]

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general