Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

Time To First API Call - Google Sheets

Steps to reproduce:

  1. 00:00 - Sign in to Google Cloud Console

  2. 00:30 - Grab or create your OAuth2.0 credentials

  3. 03:20 - Make sure you've enabled the Google Sheets API - https://console.cloud.google.com/apis/api/sheets.googleapis.com/overview

  4. 03:35 - Preparing the Google Sheets Integration

    mkdir ttfac-googlesheets
    cd ttfac-googlesheets
    npm init --yes
    touch index.js
    code .
    
  5. 04:00 - Trying to find a great OAuth2 package - https://www.google.com/search?q=oauth+npm+package

  6. 05:27 - Client-oauth2 seems like a good fit https://www.npmjs.com/package/client-oauth2

  7. 05:28 - yarn add client-oauth2

  8. 08:36 - Looking at the README. Find the snippet below which works for GitHub:

    var express = require('express')
    var app = express()
    
    app.get('/auth/github', function (req, res) {
    var uri = githubAuth.code.getUri()
    
    res.redirect(uri)
    })
    
    app.get('/auth/github/callback', function (req, res) {
       githubAuth.code.getToken(req.originalUrl)
        .then(function (user) {
          console.log(user) //=> { accessToken: '...', tokenType: 'bearer', ... }
    
          // Refresh the current users access token.
          user.refresh().then(function (updatedUser) {
            console.log(updatedUser !== user) //=> true
            console.log(updatedUser.accessToken)
          })
    
          // Sign API requests on behalf of the current user.
          user.sign({
            method: 'get',
            url: 'http://example.com'
          })
    
          // We should store the token into a database.
          return res.send(user.accessToken)
     })
    })
    

    Seems OK.

  9. 08:38 - yarn add express

  10. 09:00 - The snippet is missing a listener on app app.listen(300)

  11. 10:30 -Spend a minute to look at Express documentation and have something that logs out the right path. Figure it out by using:

    app.listen(3000, null, () => {
      console.log("http://localhost:3000/");
    });
    
  12. 11:28 - node index.js

  13. 11:35 - Manage to redirect to the GitHub redirection URL using the snippet provided on the README. Now updating the snippet to use Google Sheets

  14. 12:05 - Looking at Google Sheets authorization URL

  15. 14:00 - Find out Google has a client library for JS - https://github.com/google/google-api-javascript-client. Might give it a try next time!

  16. 16:05 - Find that URL: https://accounts.google.com/o/oauth2/v2/auth

  17. 16:31 - Trying out again. I'm redirected to Google consent screen, but I'm facing an error:

    Error 400: redirect_uri_mismatch
    The redirect URI in the request, http://localhost:3000/auth/googlesheets/callback, does not match the ones authorized for the OAuth client. To update the authorized redirect URIs, visit: https://console.developers.google.com/apis/credentials/oauthclient/488776293043-krv7po4t3eecnub28mf4p3perkbe3ncc.apps.googleusercontent.com?project=488776293043
    
  18. 18:30 - I have updated the redirect URI in Google Cloud Console. Tried again. A new error:

    Authorisation Error
    Error 400: invalid_scope
    Some requested scopes were invalid. {invalid=[gist, notifications]}
    Learn more
    
  19. 19:04 - Updating the scopes. Now using: https://www.googleapis.com/auth/spreadsheets.readonly

  20. 19:44 - Seems ok on the OAuth dance side, but I've this error:

    http://localhost:3000/
    (node:44973) UnhandledPromiseRejectionWarning: TypeError: Body is destroyed
    
  21. 26:50 - After looking at the client-oauth2 package, I'm starting to think that my token URL isn't right. I couldn't figure on Google OAuth doc, what was the right URL to use. Desperately looking at bearer/pizzly repo, I can confirm that the URL should be https://www.googleapis.com/oauth2/v4/token Thanks Google ;)

  22. 27:00 - Trying again. Got my first access_token!

  23. 28:51 - Looking around the Google Sheets API. I can see they have an SDK - https://developers.google.com/sheets/api/quickstart/nodejs. I knew it, but wanted to use a third-party OAuth package here ^^

  24. 29:28 - Just mentionning that the /token isn't referenced as well on the SDK page.

  25. 30:19 - I've found a nice endpoint to try out: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get

  26. 30:32 - Open Google Drive to create a test spreadsheet.

  27. 31:19 - Here we go: https://docs.google.com/spreadsheets/d/1X3j5I52WkY8JrJNvRigGYZK4UExKwRPgWHYmcv_Rjc8/edit?usp=sharing

  28. 32:29 - Trying the endpoint in Google Sheets' documentation tool. Works great! Technically, it let me perform a TTFAC in less than 30 seconds... Even if I believe I saved myself 5 minutes by knowing how the range parameter should be passed.

  29. 33:51 - Trying again with:

    const spreadsheetId = "1X3j5I52WkY8JrJNvRigGYZK4UExKwRPgWHYmcv_Rjc8";
    const range = "Sheet1";
    
    // Sign API requests on behalf of the current user.
    user.sign({
      method: "get",
      url: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}`,
    });
    
  30. 36:03 - Haven't tried yet. When having a look at what user.sign does, it returns a URL with the authentication part properly set. So the code needs a little bit of update. Here's what I'm testing:

    // Sign API requests on behalf of the current user.
    const request = user.sign({
       method: "get",
       url: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}`,
    });
    
    // Request the Google Sheets API
    const res = await fetch(request);
    const data = await res.text();
    console.log(data)
    
  31. 43:00 - Got an error. The thing is I wanted to use the same HttpClient than client-oauth2 (popsicle), but I'm having an error. Trying again with axios. Here's the final version:

    const axios = require("axios");
    var express = require("express");
    var app = express();
    
    var ClientOAuth2 = require("client-oauth2");
    
    var googleSheetsAuth = new ClientOAuth2({
      clientId: "*********************.apps.googleusercontent.com",
      clientSecret: "*******************",
      accessTokenUri: "https://www.googleapis.com/oauth2/v4/token",
      authorizationUri: "https://accounts.google.com/o/oauth2/v2/auth",
      redirectUri: "http://localhost:3000/auth/googlesheets/callback",
      scopes: ["notifications", "gist"],
    });
    
    // http://localhost:3000/auth/googlesheets
    app.get("/auth/googlesheets", function (req, res) {
      var uri = googleSheetsAuth.code.getUri();
    
      res.redirect(uri);
    });
    
    // http://localhost:3000/auth/googlesheets/callback
    app.get("/auth/googlesheets/callback", function (req, res) {
      googleSheetsAuth.code.getToken(req.originalUrl).then(async function (user) {
        // console.log(user); //=> { accessToken: '...', tokenType: 'bearer', ... }
    
        const spreadsheetId = "1X3j5I52WkY8JrJNvRigGYZK4UExKwRPgWHYmcv_Rjc8";
        const range = "Sheet1";
    
        // Sign API requests on behalf of the current user.
        const request = user.sign({
          method: "get",
          url: `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${range}`,
        });
    
        // Request the Google Sheets API
        const result = await axios.request(request);
    
        // We should store the token into a database.
        return res.send(result.data);
      });
    });
    
    app.listen(3000, null, () => {
      console.log("http://localhost:3000/");
    });
    
  32. 43:19 - Success!

    {"range":"Sheet1!A1:Z1000","majorDimension":"ROWS","values":[["Hello, Google Sheets API!"]]}
    
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.