Use Google OAuth to create token for fetching data from Google Sheets

README.md

• Create a service account, I don't exactly remember but I think you'll get an email after creating a service account.
• Add this email as an editor to your Google Sheet (Please research on this, I don't exactly remember)

Below code can be used on Edge (Cloudflare Workers)

You probably get the expiry time of the token in the token itself, you can use that to refetch new token again.

getGoogleOAuthToken.ts

const PEM_HEADER: string = "-----BEGIN PRIVATE KEY-----";
const PEM_FOOTER: string = "-----END PRIVATE KEY-----";

// Simplify binding the env var to a typed object
export interface GoogleKey {
  type: string;
  project_id: string;
  private_key_id: string;
  private_key: string;
  client_email: string;
  client_id: string;
  auth_uri: string;
  token_uri: string;
  auth_provider_x509_cert_url: string;
  client_x509_cert_url: string;
}

// Inspiration: https://gist.github.com/markelliot/6627143be1fc8209c9662c504d0ff205
//
// GoogleOAuth encapsulates the logic required to retrieve an access token
// for the OAuth flow.
class GoogleOAuth {
  constructor(public googleKey: GoogleKey, public scopes: string[]) {}

  public async getGoogleAuthToken({ sub }): Promise<string | undefined> {
    const { client_email: user, private_key: key } = this.googleKey;
    const scope = this.formatScopes(this.scopes);
    const jwtHeader = this.objectToBase64url({ alg: "RS256", typ: "JWT" });

    try {
      const assertiontime = Math.round(Date.now() / 1000);
      const expirytime = assertiontime + 3600;
      const claimset = this.objectToBase64url({
        iss: user,
        scope,
        aud: "https://oauth2.googleapis.com/token",
        exp: expirytime,
        iat: assertiontime,
        sub,
      });

      const jwtUnsigned = `${jwtHeader}.${claimset}`;
      const signedJwt = `${jwtUnsigned}.${await this.sign(jwtUnsigned, key)}`;
      const body = `grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Ajwt-bearer&assertion=${signedJwt}`;

      const response = await fetch(this.googleKey.token_uri, {
        method: "POST",
        headers: {
          "Content-Type": "application/x-www-form-urlencoded",
          "Cache-Control": "no-cache",
          Host: "oauth2.googleapis.com",
        },
        body,
      });

      const resp = await response.json();
      return resp.access_token;
    } catch (err) {
      console.error(err);
      return undefined;
    }
  }

  private objectToBase64url(object: object): string {
    return this.arrayBufferToBase64Url(
      new TextEncoder().encode(JSON.stringify(object))
    );
  }

  private arrayBufferToBase64Url(buffer: ArrayBuffer): string {
    return btoa(String.fromCharCode(...new Uint8Array(buffer)))
      .replace(/=/g, "")
      .replace(/\+/g, "-")
      .replace(/\//g, "_");
  }

  private str2ab(str: string): ArrayBuffer {
    const buf = new ArrayBuffer(str.length);
    const bufView = new Uint8Array(buf);
    for (let i = 0, strLen = str.length; i < strLen; i += 1) {
      bufView[i] = str.charCodeAt(i);
    }
    return buf;
  }

  private async sign(content: string, signingKey: string): Promise<string> {
    const buf = this.str2ab(content);
    const plainKey = signingKey
      .replace(/(\r\n|\n|\r)/gm, "")
      .replace(PEM_HEADER, "")
      .replace(PEM_FOOTER, "")
      .trim();

    const binaryKey = this.str2ab(atob(plainKey));
    const signer = await crypto.subtle.importKey(
      "pkcs8",
      binaryKey,
      {
        name: "RSASSA-PKCS1-V1_5",
        hash: { name: "SHA-256" },
      },
      false,
      ["sign"]
    );
    const binarySignature = await crypto.subtle.sign(
      { name: "RSASSA-PKCS1-V1_5" },
      signer,
      buf
    );
    return this.arrayBufferToBase64Url(binarySignature);
  }

  // formatScopes will create a scopes string that is formatted for the Google API
  private formatScopes(scopes: string[]): string {
    return scopes.join(" ");
  }
}

const getGoogleAuthToken = async ({
  serviceAccount,
  sub,
}: {
  serviceAccount: string;
  sub: string;
}) => {
  const scopes = ["https://www.googleapis.com/auth/spreadsheets.readonly"];
  const googleAuth = JSON.parse(serviceAccount);
  const oauth = new GoogleOAuth(googleAuth, scopes);
  const token = await oauth.getGoogleAuthToken({
    sub,
  });
 
  return token;
};

export default getGoogleAuthToken;

getGoogleSheetData.ts

import getGoogleAuthToken from "./getGoogleOAuthToken";

async function getGoogleSheetData(
  range,
  { DOC_ID, SERVICE_ACCOUNT_CREDS, EMAIL_SUB }
) {
  const token = await getGoogleAuthToken({
    serviceAccount: SERVICE_ACCOUNT_CREDS,
    sub: EMAIL_SUB, // Don't exactly remember but I think you get this email after creating a service account, and since you have added this email as an editor, the token will be created for this email
  });
  const URL = `https://sheets.googleapis.com/v4/spreadsheets/${DOC_ID}/values/${encodeURIComponent(
    range
  )}`;

  const resp = await fetch(URL, {
    headers: {
      Authorization: `Bearer ${token}`,
    },
  });
  const csv = await resp.json();
  return csv;
}

export default getGoogleSheetData;