TTFAC - Google Sheets.md

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!"]]}