How to Access Google Sheets Using API? · · Blog (2023)

Google Sheets – with its super clean interface and effortless visualization – is every knowledge worker’s sweetheart these days. From simple data entry to project management, the mighty Google Sheets unleashes all the possibilities.

But for developers, working directly with a spreadsheet can be annoying. If you are hinting about CSV files, we can conclude they are not nifty for our workflows.

This marks the dawning of!

After spending tons of hours on new projects and tools, we figured there had to be a simple way of using spreadsheets as a database. A nice layer of REST API on the spreadsheet would be a cherry on top.

From today’s tutorial, let’s find out how to build a cryptocurrency price tracker straight from Google Sheets.

We’ll set up the spreadsheet with some data, add it to the, and show how the spreadsheet transforms into REST APIs in less than 60 seconds.

After that, we’ll show you how to get line of best fit on Google Sheets so it’s easier to visualize trends, gain valuable insights, and share your findings with others.

Let’s get started!

Step 1: Fire up the Google Sheet

Open a new blank Google Sheet and input your data.

How to Access Google Sheets Using API? · · Blog (1)

Here we’ve put together a data set exhibiting the cryptocurrency prices.

Step 2: Generate a shareable link

From the Share option at the top, create a shareable link for the file. Change the privacy to “Anyone with the link on the internet can View.”

(Video) Google Sheets API | Get Google Spreadsheet

How to Access Google Sheets Using API? · · Blog (2)

Copy the link to your clipboard.

Step 3: Drop to

Next up, type on your browser and flick on the +Connection button.

Step 4: Paste the spreadsheet URL

On the Connection URL, paste the spreadsheet URL.

How to Access Google Sheets Using API? · · Blog (3)

Make sure the origin is marked as Google Spreadsheet.

Tap on Connect, and voila. You have created a brand new REST API for your data set.

Step 5: Copy the API or Connection URL

How to Access Google Sheets Using API? · · Blog (4)
How to Access Google Sheets Using API? · · Blog (5)

Copy the API or Connection URL to your clipboard.

Step 6: Dive on Postman

How to Access Google Sheets Using API? · · Blog (6)

You can quickly verify the feasibility of the API from your browser.

(Video) Google Sheets API - How you can access any Google Spreadsheet using an API

What we do in is paste the APIs into Postman.

If you’ve never heard of Postman, it’s a straightforward way to send requests to APIs.

Step 7: Wrapping up with JSON data

Move to the Explore section from Postman.

How to Access Google Sheets Using API? · · Blog (7)

Paste the API URL, and hit Send.

And boom! The spreadsheet is all transformed to JSON data.

Protip: Visualize data with line of best fit on Google Sheets

Now that you have your API up and running, let’s explore some fun visualization techniques to bring your data to life.

One of the most underrated features of the Google Sheets best fit line.

This is basically a fancy way of saying “let’s draw a line that kinda sorta fits the trend in our data points”. But don’t be fooled by the simplicity of this visualization – it can help you identify correlations and make smarter decisions based on your data.

For example, imagine you’ve been tracking the price of Bitcoin over the past year. By adding a line of best fit in Google Sheets, you may be able to identify if the price is on an upward or downward trend. You can use this to help you decide if it’s a good time to buy, sell, or hold your BTC.

Here’s how to make a line of best fit in Google Sheets.

(Video) How to access an API with Google Apps Script

Step 1: Create a scatter chart

Before we actually get a best fit line in Google Sheets, we’ll need create a scatter chart, which looks something like this:

How to Access Google Sheets Using API? · · Blog (9)

First, select the data range you want to include in your chart, and don’t forget to give your columns some sweet headers.

Next, hit up the Insert menu in the top navigation bar and click on “Charts”. A chart will automatically appear on your sheet, and then you’ll see the “Chart Editor” sidebar pop up on the right side of your screen.

Now, Google Sheets is pretty smart and will try to recommend a chart type based on your data. But it’s a scatter chart you’re after, no worries!

So go to the “Setup” tab in the Chart Editor and find the drop-down menu below “Chart Type”. Choose “Scatter Chart” from the options, and voila!

Easy peasy, right?

Step 2: Adding Google Sheets line of best fit

Now that we have our scatter chart up and running, it’s time to add a line of best fit to give us a clearer picture of the trend. Luckily, Google Sheets makes this super easy!

First, select the Customize tab from the Chart Editor sidebar, which should still be open on the right side of your window. If not, double click the chart and it will show.

How to Access Google Sheets Using API? · · Blog (10)

Next, look for the Series drop-down menu, which should show you all the data series you have in your chart.

How to Access Google Sheets Using API? · · Blog (11)

Once you’ve located it, scroll all the way down to the bottom of the drop-down menu. You should see three checkboxes there, waiting for you to tick them off.

The one we want is the Trend Line checkbox, which will add the line of best fit to our chart.

How to Access Google Sheets Using API? · · Blog (12)

And that’s it! You should now have something that looks like this:

(Video) Google Sheets… Your Next Database?

How to Access Google Sheets Using API? · · Blog (13)

With just a few clicks, we now have a chart that not only displays our data but also shows us the overall trend and direction. Pretty cool, right?

Customizing your Google Sheets best fit trendline

Google Sheets offers you a plethora of options to customize your trendlines.

You can take your data analysis to the next level by choosing from different types of trendlines, including exponential, linear, logarithmic, power series, moving average, or polynomial trendlines.

And that’s not all! You can also customize the color, thickness, and opacity of your trendlines so that they fit your needs perfectly.

So, whether you want your trendline to be neon green and three pixels thick, or a subtle shade of blue and half-transparent, you can make it happen. The best part is that you don’t need to be a design wizard or coding genius to do it!

Expect no less than Google Sheets to make adding trend and best fit lines easy as possible.

Benefits of a best fit line in Google Sheets

Now that you know how to do line of best fit on Google Sheets, you might wonder, “Why the heck should this matter?”

It’s actually a useful tool for anyone who wants to analyze trends and make data-driven decisions. Here are some benefits of Google Sheets line of best fit:

  • Identify trends: A best fit line helps you see the overall trend in your data. Whether you’re tracking cryptocurrency prices or sales figures, the best fit line will give you a quick and easy way to identify whether the data is trending up, down, or staying relatively stable.
  • Make predictions: Using a best fit line, Google Sheets helps you make predictions about where the data will go in the future. Of course, these predictions should be taken with a grain of salt, but it can be useful for making informed decisions based on the data you have.
  • Visualize relationships: It makes it easier to visualize the relationship between two variables. For example, if you’re tracking the price of a particular cryptocurrency against the total volume of transactions, it will help you see how these two variables are related to each other.
  • Communicate insights: Finally, knowing how to get line of best fit on Google Sheets can also help you communicate insights to others. Whether you’re presenting to your boss or sharing data with a team, a best fit line can make the information more digestible to other people.

So don’t be intimidated by the idea of adding a best fit line to your Google Sheets charts. Just a few mouse clicks, and you can gain valuable insights using data that you have on hand.


Now that our simple API is ready to hit the station, we can create, update, delete (CRUD) on our first-hand backend. Additionally, we can develop a simple UI in React or Vue to show these prices to the concerned users.

And don’t forget the power of the line of best fit in Google Sheets – it can help you better understand the trends and patterns in your data.

If you want to notch things up a bit, pull out other crypto APIs and combine them in the existing spreadsheet. This should put a good show for the users.

(Video) How to Embed Google Sheets Into Your Website or Blog

The knack for using Google sheet as a REST API is undefinable, but we did have some fun tweaking with crypto prices.

Hopefully, the fun little venture unleashes some of your unique ideas. If you’ve anything fiery for the blog, just shoot!


How do I access Google Sheets through API? ›

Enable the Google Sheets API
  1. Log in to your Google Cloud Console. ...
  2. Click the menu to access “APIs & Services”, then “Enabled APIs & services”. ...
  3. Click the button that says “+ ENABLE APIS AND SERVICES”. ...
  4. Search for the Google Sheets API, as shown below and click on the result. ...
  5. Click to enable the Google Sheets API.
Jan 11, 2023

How do I get data from API to spreadsheet? ›

Import the API data as XML
  1. In Microsoft Excel, select the File tab, then go to Options > Customize Ribbon.
  2. In the Customize the Ribbon drop-down menu select Main Tabs.
  3. Select the Developer check box then OK to apply.

How do I get Google spreadsheet answers? ›

On your computer, open a spreadsheet in Google Sheets. If you want to ask questions about data that's on a different sheet, at the top right click Edit and make your changes. Under "Answers," enter your question in the box and press Enter. To find answers, click the question under the text box.

How do I give a full access to a Google sheet? ›

Sheets: Share with specific people or using a link
  1. Open the file you want to share (that you own or have edit access to).
  2. Click Share.
  3. Enter the email addresses or groups that you want to share with.
  4. Choose what kind of access you want to give people: Editor, Commenter, or Viewer.
  5. Click Send.

How do I get data from Google API? ›

Go to the Google Cloud console API Library. From the projects list, select the project you want to use. In the API Library, select the API you want to enable. If you need help finding the API, use the search field and/or the filters.

Can you query API in Google Sheets? ›

Google Sheets Query uses the Google API Query Language to retrieve and manipulate data from the Google Sheets API. For example, you want to retrieve all rows from a sheet where the value in column A is greater than 10.

How do I pull all data from an API? ›

The most straightforward way of accessing data from an API endpoint is simply viewing it in the browser. As long as you're connected to the internet, you should be able to preview most API's data regardless of its format.

How do I access data through API? ›

How to Use an API
  1. Select an API. First things first, you'll want to find an API you could incorporate into your business. ...
  2. Get an API key. ...
  3. Review the API documentation. ...
  4. Write a request to an endpoint. ...
  5. Connect your app.
Apr 28, 2021

How do I collect data from Google Sheets? ›

Go to the page with the form, open the Content panel of the form block, and select the "GSHEET" checkbox. Publish the page. As form responses are received, Google Sheets will automatically add the fields in the same order as they have been specified in the Content panel of your form: email, name, phone, for example.

How do I analyze a Google Sheet? ›

Sheets: Get automatic charts
  1. In a spreadsheet, select a range of cells, columns, or rows. Otherwise, you'll get insights based on where your cursor is.
  2. At the bottom, click Explore .
  3. Choose an option: Point to a chart and click Insert chart . Ask a question about your data under Answers. The results can include charts.

How do I fetch data from Google Sheet to Google form? ›

Can Google Forms Pull Data From a Spreadsheet? No, you can't use a Google Form to pull data from a spreadsheet. But you can opened forms directly from Google Sheets by navigating to Tools > Create a new form.

Can anyone access Google Sheet? ›

You can share the files and folders that you store in Google Drive with anyone. When you share from Google Drive, you can control whether people can edit, comment on, or only view the file.

Why can't I access Google Sheets? ›

Tip: If you're using other browsers, you can also clear cache & cookies on Safari, Firefox, and Microsoft Edge. Some extensions in your browser may interfere with Docs, Slides or Sheets. Try opening your file using an incognito window and log in with your Google account.

How many people can access a Google Sheet at once? ›

Share & collaborate on a file with many people. At any time, a Google Docs, Sheets, or Slides file can only be edited on up to 100 open tabs or devices. If there are more than 100 instances of the file open, only the owner and some users with editing permissions can edit the file.

Is Google Sheets API free? ›

All use of the Google Sheets API is available at no additional cost.

Are Google Apis free? ›

All use of Google Search Console API is free of charge. However, it is subject to usage limits. Was this helpful?

Can you download data from API? ›

There are two things you need to access and download data, the URL to the data table in the API and a query to specify what data you want to download. I will show you how both are obtained in a very intuitive step-by-step process.

How do I pull information from another sheet in Google Sheets? ›

Get data from other sheets in your spreadsheet

Select a cell. Type = followed by the sheet name, an exclamation point, and the cell being copied. For example, =Sheet1! A1 or ='Sheet number two'!

Can you query an API? ›

API Query is a generic query component to read data from JSON and XML based APIs. An API is described in a "Profile" definition, which is a collection of XML files describing the API and mapping it to tables, rows, and columns.

How do I run an API script? ›

Execute Functions with the Apps Script API
  1. Step 1: Set up the common Cloud project.
  2. Step 2: Deploy the script as an API executable.
  3. Step 3: Configure the calling application.
  4. Step 4: Make the request.
Apr 13, 2023

How to get data from API JSON? ›

To get JSON data from an API endpoint, you must first call the fetch() method and then response. json(). The fetch() method returns a Promise representing the server's response, and the response. json() resolves to the result of parsing the JSON response into a JavaScript object.

How to use Postman to get data from API? ›

GET Request in Postman
  1. Step 1: Create a request. ...
  2. Step 2: Enter the URL in the address bar. ...
  3. Step 3: Now, select the GET request from the list of request methods.
  4. Step 4: Now press send.
  5. Step 5: Once you press the send button, you will get the response from the server.

Why use API instead of database? ›

If a malicious user had direct access to the database table, they could ruin the database entirely. In comparison, APIs allow for controls to be put into place that ensure that only valid users have access to the database and that only valid requests can modify the data within the database.

How do I push data from API? ›

Abstract. The Push API enables sending of a push message to a web application via a push service. An application server can send a push message at any time, even when a web application or user agent is inactive. The push service ensures reliable and efficient delivery to the user agent.

Does an API need a database? ›

In order to build a public API, you'll need the following: A backend with routing of some sort as mentioned above. A database where your application can store its data.

What is an example of an API access? ›

Real-Life Applications Related to API Access

For example, when you add a product to your cart online, it is an API that communicates with the website, telling the site that a product was added. In turn, your cart is updated with that product. Another example is booking a flight.

What is API for dummies? ›

APIs, or Application Programming Interfaces, are like a set of rules and protocols that allow different software programs to talk to each other and share data or functionality. For example, when you use a weather app on your phone, it's probably using an API to get the current weather data from a service.

What is API and how do you access it? ›

APIs are mechanisms that enable two software components to communicate with each other using a set of definitions and protocols. For example, the weather bureau's software system contains daily weather data. The weather app on your phone “talks” to this system via APIs and shows you daily weather updates on your phone.

How do I get JSON data from Google Sheets? ›

  1. Launch Google Sheets and create a new spreadsheet.
  2. Go to Tools > Script editor.
  3. Copy and paste the code from the file into the file. ...
  4. Return to the Google sheet you created and type =GETJSON in a cell to start using the functions.

How do I extract data from JSON in Google Sheets? ›

Part I.) Use a free ImportJSON Script
  1. Open a Google Sheet. Open a new or existing Google Sheet. ...
  2. Open Script Editor. ...
  3. In the script editor, remove the placeholder content. ...
  4. Copy and paste an open-source script. ...
  5. Save and name the script. ...
  6. Go back to your Google Sheet. ...
  7. Add your JSON API URL. ...
  8. Import your JSON data.

Where is Google sheet data stored? ›

Your content is stored securely

When you create a file on Google Docs, Sheets, & Slides and share it, it is stored securely in our world-class data centers. Data is encrypted in-transit and at-rest. If you choose to access these files offline, we store this info on your device.

Does Google have a data analysis tool? ›

Google Analytics gives you the tools, free of charge, to understand the customer journey and improve marketing ROI.

How do I check access to a Google sheet? ›

You can open Sheets in any of the following ways:
  1. Any web browser—Go to
  2. Google Drive—Click New. ...
  3. Most Google pages—In the upper-right corner, click the App Launcher. ...
  4. Android devices—Install and open the Android app.
  5. Apple iOS devices—Install and open the iOS app.

Which app allows you to analyze data in Google? ›

Google Sheets has gone beyond allowing users to create simple tables and basic functions. With add-ons, you can automate critical parts of your data analysis processes and even transform the way you handle and report information.

How do I get an access token for Google Sheet API? ›

Basic steps
  1. Obtain OAuth 2.0 credentials from the Google API Console. ...
  2. Obtain an access token from the Google Authorization Server. ...
  3. Examine scopes of access granted by the user. ...
  4. Send the access token to an API. ...
  5. Refresh the access token, if necessary.

Can I populate a Google form from a spreadsheet? ›

You can create a new spreadsheet for responses in Google Sheets or choose from your existing spreadsheet in Google Sheets to store responses. Once you link your google form with a spreadsheet, the new responses will automatically synced. To use this feature, you need our Gsuite addon.

How do I auto generate a Google form from a spreadsheet? ›

Create a form from a Google spreadsheet:

Click the Tools drop-down menu, scroll to Form, and select Create a form. The form responses will be collected in the tab from which you create the form. 2. In the form template that opens, you can add any questions and options you'd like.

Can someone without a Google Account access Google Sheets? ›

You can associate any existing email address with a Google Account. Alternatively, files can be shared with non-Google accounts using visitor sharing.

Can someone see if you view their Google Sheet? ›

File activity includes the names of users who have viewed Google Docs, Sheets, and Slides files and the time they viewed them. Using this data, file editors can make informed decisions about following up with collaborators.

Can you see who opened your Google Sheet? ›

See which of your collaborators have opened your file in your Activity dashboard. In Google Docs, Sheets, and Slides you can see when your file was last viewed and by who. Giving you the important information of who you need to chase.

How do I access restricted Google Sheets? ›

When you change an item's general access to Restricted, only people with access can open the file.
  1. Find the file or folder in Google Drive, Google Docs, Google Sheets, or Google Slides.
  2. Open or select the file or folder.
  3. Click Share or Share. ...
  4. Under “General access”, click the Down arrow .
  5. Select Restricted.
  6. Click Done.

How do I access Google Docs without permission? ›

Share your Google Drive files publicly with no sign in required
  1. Select the blue Share button in the top right-hand side of your chosen Drive file.
  2. Select > Advanced in the bottom right-hand side, select > Change.
  3. You'll see a range of Link Sharing options.
  4. Choose > On - public on the web > Choose Save.

How do I bypass Google Drive permissions? ›

Try to Use Incognito or InPrivate Mode. According to feedbacks from some Google Drive users, the incognito or InPrivate mode will allow you to be able to browse without having your activity being remembered, which is also one way for you to be able to fix the problem that you access to Google Drive denied.

Can a Google Sheet have 2 owners? ›

You can invite another person with a personal Google Account to take ownership of your files and folders. You can't invite another person with a work or school account to take ownership of your files or folders. The person you invite to own the file or folder must accept your request to complete the transfer.

What is the maximum size of a Google Sheet? ›

Google Spreadsheets

5,000,000 cells, with a maximum of 256 columns per sheet. Uploaded spreadsheet files that are converted to the Google spreadsheets format can't be larger than 20 MB, and need to be under 400,000 cells and 256 columns per sheet. Check out our detailed spreadsheet size limits tip.

What is the maximum Google Sheet rows? ›

In addition to the restriction on the number of cells, Google Sheets also has the following constraints: Google Sheets can process 18,278 columns. The upper limit for rows is 40,000. A Google Sheet workbook can have 200 sheets.

Is Google sheet API free? ›

All use of the Google Sheets API is available at no additional cost.

How do I request access to Google Sheets app? ›

Open the file. Click Request edit access. If you want, add a note to the owner of the file. Click Send request.

How to access Google Sheets from JavaScript? ›

Steps to access Google Sheets
  1. Get OAuth Credentials and API keys and configure them into an application.
  2. Authenticate and Authorise the app to allow accessing Google sheets.
  3. Read spreadsheet data and store it in an array.
  4. Parse response data and display them on the UI.
Oct 11, 2022

How do I create a Google spreadsheet API? ›

Google Sheets API Setup
  1. Create a Service Account. Create a Google Developer Account. Create a Google Developer Project. Enable the Google Sheeets API. Creating a Service Acccount.
  2. Linking the Google Sheets Document. Create the Google Sheets Document. Share the Google Sheets Document. Record Relevant Information.

How much does Google Sheets API cost? ›

All use of the Google Docs API is available at no additional cost. Exceeding the quota request limits doesn't incur extra charges and your account is not billed.

Does Google API cost money? ›

All use of Google Search Console API is free of charge. However, it is subject to usage limits.

What can you do with Google Sheets API? ›

The Google Sheets API is a RESTful interface that lets you read and modify a spreadsheet's data.
The most common uses of this API include the following tasks:
  1. Create spreadsheets.
  2. Read and write spreadsheet cell values.
  3. Update spreadsheet formatting.
  4. Manage Connected Sheets.

Can anyone access Google Sheets? ›

The content you save on Google Docs, Sheets, & Slides is private to you, from others, unless you choose to share it. Learn how to share or stop sharing files in Google Docs, Sheets, & Slides. Google respects your privacy. We access your private content only when we have your permission or are required to by law.

Can everyone access Google Sheets? ›

You can choose if your file should be available to anyone or restricted to only the people with access. If you allow access to anyone with the link, your folder won't restrict who can access it. Select the file you want to share. Choose who can access the file.

How to use Google sheet API in JavaScript? ›

Create an API key
  1. In the Google Cloud console, go to Menu menu > APIs & Services > Credentials. Go to Credentials.
  2. Click Create credentials > API key.
  3. Your new API key is displayed. Click Copy content_copy to copy your API key for use in your app's code.

Can Python access Google Sheets? ›

In order to read from and write data to Google Sheets in Python, we will have to create a Service Account. A service account is a special kind of account used by an application or a virtual machine (VM) instance, not a person.

How do you open a sheet in google App Script? ›

  1. Click Extensions > Apps Script to open the script editor, then copy the script text from the original spreadsheet and paste it into the script editor of another spreadsheet.
  2. Make a copy of the spreadsheet that contains the custom function by clicking File > Make a copy.
Apr 13, 2023

What can I use instead of Google spreadsheet API? ›

Instantly Use Your Spreadsheets as an API and Database

The best alternatives to Api Spreadsheets are Sheety, Sheetsu, and Rows. If these 3 options don't work for you, we've listed a few more alternatives below. What do you think of Api Spreadsheets? Turn any Google sheet into an API instantly, for free.

How would you test a Google API and learn how it works? ›

How Do You Test a Google API?
  1. Go to the Google API Console and set up your project if it's not there already.
  2. Enable the API for the Google Application that you want to test for example Gmail, or Google Drive.
  3. Go to the credentials tab and get the Client ID and Secret.
Dec 12, 2022

How do I create an access token for Google Sheets API? ›

Obtaining Access Token and Refresh Token

Navigate to OAuth 2.0 Playground and click the OAuth 2.0 Configuration button in the top right corner of your screen. Select Use your own OAuth credentials, and provide the obtained Client ID and Client Secret values. Click on Close.


1. Create Google Spreadsheet and API from JSON data
2. Best Tools to Turn Your Google Sheets Into an API
3. Using Google Sheets with Python (COMPLETE Beginner GSpread Tutorial!)
(Millibit Tutorials: Ruchir's Coding Clips)
4. - lead gen system using Proxycurl database + google sheets demo!
(Fund Stuff)
5. How to use AI like GPT-3 in Google Sheets
(Peter Mangan)
6. Free OpenAi GPT-3 Content Creator Using Google Sheets and the API
(Mike Hayden)
Top Articles
Latest Posts
Article information

Author: Aron Pacocha

Last Updated: 2023/06/17

Views: 5692

Rating: 4.8 / 5 (68 voted)

Reviews: 91% of readers found this page helpful

Author information

Name: Aron Pacocha

Birthday: 1999-08-12

Address: 3808 Moen Corner, Gorczanyport, FL 67364-2074

Phone: +393457723392

Job: Retail Consultant

Hobby: Jewelry making, Cooking, Gaming, Reading, Juggling, Cabaret, Origami

Introduction: My name is Aron Pacocha, I am a happy, tasty, innocent, proud, talented, courageous, magnificent person who loves writing and wants to share my knowledge and understanding with you.