Guide to Google Sheets API v4 Integration

Google web services have become an essential part of many projects’ infrastructure, a vital integration element. We can no longer imagine online services without them. Meanwhile, Google developers are working on expanding the capabilities of their services, developing new APIs, and increasing the security of our data. Usually, updates are released smoothly for users and do not require any changes on your side. But not this time with the new Google Sheets API.

Preface: Progress Is a Pain

In 2021, Google introduced version 4 of its Sheets API, which is incompatible with the previous one. This affected data security and privacy. Sheets API v3 support was extended until August 2021 to provide developers with more time to migrate to the new API version. Since the end of support for the v3 API, many JavaScript developers have faced migration issues. And although Google provided a detailed migration guide, as it usually happens, several crucial details are missing from it.

As a support engineer at AnyChart, I have received and continue to deal with numerous requests for help from our JS charting library users who suddenly faced issues with feeding visualizations with data from their Google spreadsheets. It shows the problem has been and remains really topical. So I decided to make a quick Google Sheets API v4 integration guide for anyone else out there.

This article showcases a basic approach to accessing a spreadsheet document on Google Sheets and loading the data from it as apparently the most common use case.

Accessing Google Spreadsheets from JavaScript

To access a Google Sheets spreadsheet from the JavaScript code, you need google-api-javascript-client and Sheets API, as well as a configured Google project and a document itself. 

Let me walk you through all this step by step.

Configuration on Google Side

1) Create a project

Go to the Google Cloud Platform:

Create a new project:

2) Enable API

Go to “Enable APIS and services”:

Type “google sheets” in the search field to find the API:

Select “Google Sheets API”:

Enable the Google Sheets API:

3) Credentials

Go to the “Credentials” tab:

Click “Create credentials” and select “API key”:

Note: Copy and store the API key. You will need it in the JavaScript code later ({GOOGLE_API_KEY} in the JS code). 

c) Click “Restrict key”:

Note: Keep your API keys secure during both storage and transmission. Best practices for this are well covered by Google in this article. All the code snippets below are simplified for demonstration purposes and do not describe security aspects.

d) In the “Restrict key” dropdown menu, locate the “Google Sheets API” item:

e) Select it, click “OK” and “SAVE”:

4) Create a document

Create a Google Sheets document the way you usually do and fill it with some data. Set a name for the sheet with your data or copy the default one — it will be required later in the JS code ({SHEET_NAME}).

Enable access to the document via a link. You can do it by clicking on the “Share” button and choosing “Anyone with the link”. (The “Viewer” access is enough.)

Copy the ID of the document. It can be found in the document’s URL, between the “/spreadsheets/d/” and “/edit” parts. This ID will be required later in the JS code ({SPREADSHEET_ID}).

All the necessary settings on the Google side are completed. Let’s move on to an application.

Accessing Google Spreadsheet Data from JavaScript Applications

Now, I will explain how to create a simple JavaScript application that fetches the data from the spreadsheet and shows it to users. To connect the app to the Sheets API, I will use the Google API Client Library for JavaScript (aka gapi), which is well described in its GitHub repository.

1) Creating a basic JavaScript application

First of all, include the gapi library in your page using the direct link.

Add the <table> tag to the HTML code and apply the CSS code you like for the table and its future content.

In the JavaScript code, create a function that will be used for fetching the data.

const start = () => {};

Inside that function, initialize the gapi client with your Google API key created earlier. 

 gapi.client.init({
    'apiKey': '{GOOGLE_API_KEY}',
    'discoveryDocs': ["https://sheets.googleapis.com/$discovery/rest?version=v4"],
  })

Then execute a request to get values via the gapi client. In the request, you should provide the spreadsheet ID and the range of cells where the data you want to access is located.

.then(() => {
    return gapi.client.sheets.spreadsheets.values.get({
      spreadsheetId: '{SPREADSHEET_ID}',
      range: '{SHEET_NAME}!{DATA_RANGE}', // for example: List 1!A1:B6
    })
  })

If all settings are correct, the resolved promise returns a response with the fetched data. Now you can get the data from the response and populate the HTML table using a simple JS script.

.then((response) => {
    // parse the response data
    const loadedData = response.result.values;

    // populate the HTML table with the data
    const table = document.getElementsByTagName('table')[0];
    
    // add column headers
    const columnHeaders = document.createElement('tr');
    columnHeaders.innerHTML = `<th>${loadedData[0][0]}</th>
<th>${loadedData[0][1]}</th>`;
    table.appendChild(columnHeaders);

    // add table data rows
    for (let i = 1; i < loadedData.length; i++) {
      const tableRow = document.createElement('tr');
      tableRow.innerHTML = `<td>${loadedData[i][0]}</td>
<td>${loadedData[i][1]}</td>`;
      table.appendChild(tableRow);
    }
  }).catch((err) => {
  	console.log(err.error.message);
  });

To execute the code, call the load() function from the gapi library and pass the function created above as an argument.

gapi.load('client', start);

The resulting application looks like below. You are welcome to check out the full code template of this HTML table with data from Google Sheets on JSFiddle. To get your own thing like this working, just replace {GOOGLE_API_KEY}, {SPREADSHEET_ID}, {SHEET_NAME}, and {DATA_RANGE} with your own information (and don’t keep the braces).

2) Tinkering output — show the data as a chart

In real-world applications, simple HTML tables are usually not enough; we want to visualize and analyze the data. Let me show you how to create a dashboard that increases the readability of the data and brings us closer to the real-world use case. When I am on duty and asked for assistance with Google Sheets API integration, it is actually the first example I share, and basically, almost always the last as it’s very illustrative and no further help is needed.

So, let’s use the AnyChart JS library for data visualization. It includes column charts and pie charts, which would be enough for this simple dashboard.

Before anything else, add AnyChart’s base JS module to HTML:

<script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-base.min.js"></script>

Also, add <div> tags for dashboard containers and apply a suitable ID for each:

<div id="container1"></div>
<div id="container2"></div>

Most of the JavaScript code remains absolutely the same. I will just rework the code that handles the Sheets API response.

So, keep the first part of the JS code unchanged:

const start = () => {
  // Initialize the JavaScript client library
  gapi.client.init({
    'apiKey': '{GOOGLE_API_KEY}',
    'discoveryDocs': ["https://sheets.googleapis.com/$discovery/rest?version=v4"],
  }).then(() => {
    return gapi.client.sheets.spreadsheets.values.get({
      spreadsheetId: '{SPREADSHEET_ID}',
      range: '{SHEET_NAME}!{DATA_RANGE}', // for example: List 1!A1:B6
    })
  }).then((response) => {

In the response handler, parse the data to compose a structure compatible with the AnyChart API:

    const loadedData = response.result.values;
    const parsedData = {
      'header': loadedData.shift(),
      'rows': loadedData,
    };

Now we’ve got everything we need to create and configure charts for the dashboard: 

    // create an instance of a column chart
    const columnChart = anychart.column();

    // set the data
    columnChart.data(parsedData);

    // configure chart appearance settings
    columnChart.title('Sales volume by manager');
    columnChart.xAxis().title('Manager');
    columnChart.yAxis().title('Sales volume, $');

    // set the container element and draw the chart
    columnChart.container('container1').draw();

    // create a pie chart likewise
    const pieChart = anychart.pie(parsedData);
    pieChart.title('Sales volume distribution in the department');
    pieChart.legend().itemsLayout('vertical').position('right');
    pieChart.container('container2').draw();

Then goes the same ending part as with the HTML table — let’s recall it just in case:

  }).catch((err) => {
  	console.log(err.error.message);
  });
};

// load the JavaScript client library
gapi.load('client', start);

Below is what the resulting dashboard looks like. You can check out the full template code of this dashboard visualizing data from Google Sheets using the v4 API on JSFiddle. To get your own project like this, simply put your own information in place of {GOOGLE_API_KEY}, {SPREADSHEET_ID}, {SHEET_NAME}, and {DATA_RANGE} (and don’t keep the braces).

Epilogue and Links

​I hope this article will be helpful to anyone who decides to build an app that uses data from Google Sheets and access it from JavaScript applications. If you have any further questions, please feel free to get in touch with me and I will be happy to do my best to help you out.

For your convenience, here is a list of all useful links from this article, in one place:

Prerequisites

• Google Sheets API: https://developers.google.com/sheets/api
• Google Sheets API migration guide: ​​https://developers.google.com/sheets/api/guides/migration
• Google API JavaScript client: https://github.com/google/google-api-javascript-client/blob/master/docs/start.md
• Securing a key: https://cloud.google.com/docs/authentication/api-keys#securing_an_api_key

Integration examples

• HTML table template on JSFiddle: https://jsfiddle.net/7ngrfpxk/
• JavaScript dashboard template on JSFiddle: https://jsfiddle.net/82hn9dmL/

Original article source at: https://www.sitepoint.com/

#googlesheets #api #integration 

What is GEEK

Buddha Community

API Integration Practices and Patterns

We all hear it so often that we almost stop hearing it: “Integration is critical to meeting users’ needs.”

Integration work consumes 50%-80% of the time and budget of digital transformation projects, or building a digital platform, while innovation gets only the leftovers, according to SAP and Salesforce. And as everyone from legacy enterprises to SaaS startups launches new digital products, they all hit a point at which the product cannot unlock more value for users or continue to grow without making integration a feature.

If I were to sum up the one question behind all of the other questions that I hear from customers, enterprises, partners, and developers, it would be something like: “Is integration a differentiator that we should own? Or an undifferentiated but necessary feature that supports what we’re trying to accomplish?”

This Refcard won’t try to answer that question for you. Rather, no matter what type of development work you do, API integration is a fact of life today, like gravity. Why? Today, experience is paramount. The average enterprise uses more than 1,500 cloud applications (with the number growing by 20% each year). Every app needs to integrate with other systems in a fluid and ever-changing application ecosystem. So instead, I’ll share some of the common practices you’re likely to contend with as well as some patterns to consider.

---

This is a preview of the API Integrations Practices and Patterns Refcard. To read the entire Refcard, please download the PDF from the link above.

#apis #api integration #integration patterns #api cloud #api patterns #api authentication #api errors #apis and integrations

Public ASX100 APIs: The Essential List

We’ve conducted some initial research into the public APIs of the ASX100 because we regularly have conversations about what others are doing with their APIs and what best practices look like. Being able to point to good local examples and explain what is happening in Australia is a key part of this conversation.

Method

The method used for this initial research was to obtain a list of the ASX100 (as of 18 September 2020). Then work through each company looking at the following:

1. Whether the company had a public API: this was found by googling “[company name] API” and “[company name] API developer” and “[company name] developer portal”. Sometimes the company’s website was navigated or searched.
2. Some data points about the API were noted, such as the URL of the portal/documentation and the method they used to publish the API (portal, documentation, web page).
3. Observations were recorded that piqued the interest of the researchers (you will find these below).
4. Other notes were made to support future research.
5. You will find a summary of the data in the infographic below.

Data

With regards to how the APIs are shared:

• Documentation is where just the Swagger/OpenAPI docs are hosted somewhere. e.g. https://api.linkgroup.com/member/docs/index
• Portal is where an interactive portal has been developed. e.g. https://dev.telstra.com/
• The web page is where the company has a single page that isn’t interactive explaining their Public APIs. e.g. https://www.bendigoadelaide.com.au/banking-products-api.asp

#api #api-development #api-analytics #apis #api-integration #api-testing #api-security #api-gateway

A Simple Guide to Planning API Roadmaps

APIs - the current “big thing” - offer the opportunity for modern organizations to unlock new and lucrative business models. The article below covers some tips on how to spin the API flywheel and leverage its possibilities.

In the API economy, a successful service can gain popularity and be utilized in ways unpredicted and often inconceivable by its original owners. The very flexible nature of the technology opens many doors, including business collaborations, reuse in third-party products or even conquering hardware barriers by reaching a spectrum of devices.

What to consider

Taking the builder’s perspective

Important note: Most of the time API consumers are not the end-users but rather the app developers. Any new venture ought to be supported with excellent learning resources and descriptive documentation. These things combined will ensure a top-notch developer experience and encourage adoption of your product, increasing its visibility in the market.

More than the revenue

While in the simplest scenario, the most popular API business model is revenue via service charges, there are several other goals:

• **Growth **- APIs are finely-grained and, usually, short-term projects that bring lots of value. Decoupling sectors of your business and encapsulating them in the form of concise, dedicated APIs enable teams to work in parallel, encouraging company growth.
• **Reach **- By building APIs for wide use, the organization can extend its group of recipients and open new opportunities to cooperate with other players in the market and discover new collaborations.
• **Compliance **- Products and services implemented as an API ecosystem can freely exchange data between one another, whether they are internal or external.
• **Insight **- The rate of use of APIs will surely give an idea of what consumers value the most. Also, implementing APIs alone helps to clarify what pieces of your business are crucial. This helps to revise implementation details and encourages reflection of your current technological solutions.

#api #api-development #api-integration #restful-api #api-based-business-model #api-first-development #automation #rest-api

Top 10 API Security Threats Every API Team Should Know

As more and more data is exposed via APIs either as API-first companies or for the explosion of single page apps/JAMStack, API security can no longer be an afterthought. The hard part about APIs is that it provides direct access to large amounts of data while bypassing browser precautions. Instead of worrying about SQL injection and XSS issues, you should be concerned about the bad actor who was able to paginate through all your customer records and their data.

Typical prevention mechanisms like Captchas and browser fingerprinting won’t work since APIs by design need to handle a very large number of API accesses even by a single customer. So where do you start? The first thing is to put yourself in the shoes of a hacker and then instrument your APIs to detect and block common attacks along with unknown unknowns for zero-day exploits. Some of these are on the OWASP Security API list, but not all.

Insecure pagination and resource limits

Most APIs provide access to resources that are lists of entities such as /users or /widgets. A client such as a browser would typically filter and paginate through this list to limit the number items returned to a client like so:

First Call: GET /items?skip=0&take=10 
Second Call: GET /items?skip=10&take=10

However, if that entity has any PII or other information, then a hacker could scrape that endpoint to get a dump of all entities in your database. This could be most dangerous if those entities accidently exposed PII or other sensitive information, but could also be dangerous in providing competitors or others with adoption and usage stats for your business or provide scammers with a way to get large email lists. See how Venmo data was scraped

A naive protection mechanism would be to check the take count and throw an error if greater than 100 or 1000. The problem with this is two-fold:

1. For data APIs, legitimate customers may need to fetch and sync a large number of records such as via cron jobs. Artificially small pagination limits can force your API to be very chatty decreasing overall throughput. Max limits are to ensure memory and scalability requirements are met (and prevent certain DDoS attacks), not to guarantee security.
2. This offers zero protection to a hacker that writes a simple script that sleeps a random delay between repeated accesses.

skip = 0
while True:    response = requests.post('https://api.acmeinc.com/widgets?take=10&skip=' + skip),                      headers={'Authorization': 'Bearer' + ' ' + sys.argv[1]})    print("Fetched 10 items")    sleep(randint(100,1000))    skip += 10

How to secure against pagination attacks

To secure against pagination attacks, you should track how many items of a single resource are accessed within a certain time period for each user or API key rather than just at the request level. By tracking API resource access at the user level, you can block a user or API key once they hit a threshold such as “touched 1,000,000 items in a one hour period”. This is dependent on your API use case and can even be dependent on their subscription with you. Like a Captcha, this can slow down the speed that a hacker can exploit your API, like a Captcha if they have to create a new user account manually to create a new API key.

Insecure API key generation

Most APIs are protected by some sort of API key or JWT (JSON Web Token). This provides a natural way to track and protect your API as API security tools can detect abnormal API behavior and block access to an API key automatically. However, hackers will want to outsmart these mechanisms by generating and using a large pool of API keys from a large number of users just like a web hacker would use a large pool of IP addresses to circumvent DDoS protection.

How to secure against API key pools

The easiest way to secure against these types of attacks is by requiring a human to sign up for your service and generate API keys. Bot traffic can be prevented with things like Captcha and 2-Factor Authentication. Unless there is a legitimate business case, new users who sign up for your service should not have the ability to generate API keys programmatically. Instead, only trusted customers should have the ability to generate API keys programmatically. Go one step further and ensure any anomaly detection for abnormal behavior is done at the user and account level, not just for each API key.

Accidental key exposure

APIs are used in a way that increases the probability credentials are leaked:

1. APIs are expected to be accessed over indefinite time periods, which increases the probability that a hacker obtains a valid API key that’s not expired. You save that API key in a server environment variable and forget about it. This is a drastic contrast to a user logging into an interactive website where the session expires after a short duration.
2. The consumer of an API has direct access to the credentials such as when debugging via Postman or CURL. It only takes a single developer to accidently copy/pastes the CURL command containing the API key into a public forum like in GitHub Issues or Stack Overflow.
3. API keys are usually bearer tokens without requiring any other identifying information. APIs cannot leverage things like one-time use tokens or 2-factor authentication.

If a key is exposed due to user error, one may think you as the API provider has any blame. However, security is all about reducing surface area and risk. Treat your customer data as if it’s your own and help them by adding guards that prevent accidental key exposure.

How to prevent accidental key exposure

The easiest way to prevent key exposure is by leveraging two tokens rather than one. A refresh token is stored as an environment variable and can only be used to generate short lived access tokens. Unlike the refresh token, these short lived tokens can access the resources, but are time limited such as in hours or days.

The customer will store the refresh token with other API keys. Then your SDK will generate access tokens on SDK init or when the last access token expires. If a CURL command gets pasted into a GitHub issue, then a hacker would need to use it within hours reducing the attack vector (unless it was the actual refresh token which is low probability)

Exposure to DDoS attacks

APIs open up entirely new business models where customers can access your API platform programmatically. However, this can make DDoS protection tricky. Most DDoS protection is designed to absorb and reject a large number of requests from bad actors during DDoS attacks but still need to let the good ones through. This requires fingerprinting the HTTP requests to check against what looks like bot traffic. This is much harder for API products as all traffic looks like bot traffic and is not coming from a browser where things like cookies are present.

Stopping DDoS attacks

The magical part about APIs is almost every access requires an API Key. If a request doesn’t have an API key, you can automatically reject it which is lightweight on your servers (Ensure authentication is short circuited very early before later middleware like request JSON parsing). So then how do you handle authenticated requests? The easiest is to leverage rate limit counters for each API key such as to handle X requests per minute and reject those above the threshold with a 429 HTTP response. There are a variety of algorithms to do this such as leaky bucket and fixed window counters.

Incorrect server security

APIs are no different than web servers when it comes to good server hygiene. Data can be leaked due to misconfigured SSL certificate or allowing non-HTTPS traffic. For modern applications, there is very little reason to accept non-HTTPS requests, but a customer could mistakenly issue a non HTTP request from their application or CURL exposing the API key. APIs do not have the protection of a browser so things like HSTS or redirect to HTTPS offer no protection.

How to ensure proper SSL

Test your SSL implementation over at Qualys SSL Test or similar tool. You should also block all non-HTTP requests which can be done within your load balancer. You should also remove any HTTP headers scrub any error messages that leak implementation details. If your API is used only by your own apps or can only be accessed server-side, then review Authoritative guide to Cross-Origin Resource Sharing for REST APIs

Incorrect caching headers

APIs provide access to dynamic data that’s scoped to each API key. Any caching implementation should have the ability to scope to an API key to prevent cross-pollution. Even if you don’t cache anything in your infrastructure, you could expose your customers to security holes. If a customer with a proxy server was using multiple API keys such as one for development and one for production, then they could see cross-pollinated data.

#api management #api security #api best practices #api providers #security analytics #api management policies #api access tokens #api access #api security risks #api access keys

Reading Data From Google Sheet using The Google Sheet API V4 From Our Service - 5

In this video, we are going to create a structure of our service so that we can read data from Google Sheets. We will see how to get the dimensions of a google sheet that is required to read data.

We will read the entries through Laravel that we have added to the Google sheet.

Code: https://github.com/amitavdevzone/google-data-studio

#google sheet api v4 #google sheet #data