How to Use Google Sheets API with Node.js (Without SDKs or Consent Screen)

#programming #javascript #googlecloud #tutorial

Recently, I've needed to integrate with Google Sheet APIs, and even though it's part of my normal daily routine, the knowledge I gained from that experience has motivated me to write this piece of code.

Mostly, people are used to connecting with Google Tools through the well-known consent page:

But, how does Google manage API requests for server-to-server services?

Let's take a deep dive and understand how to integrate Google Sheets without OAuth popups, SDKs, or real user interaction. In this post, I’ll show you how to use the Google Sheets API with a Service Account, generate your own JWT manually, and append data using native Node.js modules only — no SDKs.

Use Case

Internal tools
Server-side automations (e.g., CRON jobs)
No user login or consent screen

Step 1: Set Up a Google Service Account

1.1. Create a Google Cloud Project

Go to Google Cloud Console, create (or select) a project.

1.2. Enable the Sheets API

Search for “Google Sheets API” in the API Library, click Enable.

1.3. Create a Service Account

Go to IAM & Admin > Service Accounts
Click Create Service Account
After creation, go to the "Keys" tab and:
- Click Add Key > Create new key
- Select JSON and download it

**It's very important to save this JSON file because the code relies on it to save data to the spreadsheet through backend calls.

DON'T FORGET THIS STEP!**

1.4. Share the Spreadsheet

Open the JSON file, copy the client_email field, and share with your Google Sheet giving Editor access:
your-service-account@your-project.iam.gserviceaccount.com

Step 2: Write Code to Append Data

We'll create a script responsible for:

Generating the access_token responsible for claiming the API with the respective scopes on it.
Process the Google Spreadsheet API call.
It is expected you to serve the JSON file in some way. I'm using AWS secret manager with my keys already processed and just passing below to my google script file.

2.1 Google Service

Here, you can name it as you want. I will move forward, declaring it as a service:

export type GoogleServiceAccountJsonObjectKey = {
  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;
  universe_domain: string;
};

const generateGoogleApiJWT = (googleServiceAccountJsonObjectKey: GoogleServiceAccountJsonObjectKey) => {
  logger.info('Starting generation of google api jwt token');
  const { client_email: clientEmail = '', private_key: privateKey, private_key_id: kid } = googleServiceAccountJsonObjectKey;
  const header = {
    alg: 'RS256',
    typ: 'JWT',
    kid,
  };

  const now = Math.floor(Date.now() / 1000);

  const payload = {
    iss: clientEmail,
    scope: 'https://www.googleapis.com/auth/spreadsheets',
    aud: 'https://oauth2.googleapis.com/token',
    iat: now,
    exp: now + ONE_HOUR_LATER,
  };

  const encodedHeader = base64url(JSON.stringify(header));
  const encodedPayload = base64url(JSON.stringify(payload));
  const unsignedToken = `${encodedHeader}.${encodedPayload}`;

  let sign;

  try {
    sign = crypto.createSign('RSA-SHA256');
    sign.update(unsignedToken);
    sign.end();
  } catch (error) {
    logger.error('Error in signing token', error);
    throw error;
  }

  const signature = sign.sign(privateKey, 'base64');

  return `${unsignedToken}.${signature}`;
};

const getAccessToken = async (jwt: string): Promise<string> => {
  logger.info('Getting google api access token');
  const params = new URLSearchParams({
    grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
    assertion: jwt,
  });

  const res = await fetch('https://oauth2.googleapis.com/token', {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body: params,
  });

  const data = (await res.json()) as { access_token?: string };
  if (!res.ok) throw new Error(JSON.stringify(data));
  if (!data.access_token) throw new Error('No access_token in response');
  return data.access_token;
};

export const submitGoogleSpreadsheet = async <T>(
  body: T extends Record<string, unknown> ? T : never,
  googleSheetsAPIKey: GoogleServiceAccountJsonObjectKey,
  spreadSheetId: string,
) => {
  logger.info('Submitting google spreadsheet');

  if (!googleSheetsAPIKey || !spreadSheetId) {
    logger.error('');
    throw new Error('Missing google sheets api key or spreadsheet id');
  }

  let jwtToken;

  try {
    jwtToken = generateGoogleApiJWT(googleSheetsAPIKey);
  } catch (err: unknown) {
    logger.error('Failed to generate google api jwt', { err });
    return send(500, {});
  }

  let accessToken;

  try {
    accessToken = await getAccessToken(jwtToken);
  } catch (err: unknown) {
    logger.error('Failed to get google api access token', { err });
  }

  const bodyAsArray = Object.values(body).map((value) =>
    value === undefined || value === null ? '' : typeof value === 'boolean' ? (value ? 'yes' : 'no') : value.toString(),
  );

  const createdAt = new Date().toLocaleDateString('en-US', { year: 'numeric', month: '2-digit', day: '2-digit' });

  const fields = [...bodyAsArray, createdAt];

  logger.info('Spreadsheet fields', JSON.stringify(fields));

  const url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadSheetId}/values/Sheet1!A2:append?valueInputOption=USER_ENTERED`;

  const response = await fetch(url, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      Authorization: `Bearer ${accessToken}`,
    },
    body: JSON.stringify({
      values: [fields],
    }),
  });

  return response.json();
};

2.2 Calling the function

type MyType = {};

const spreadsheetId = "MY_SPREADSHEET_ID";

try {
    const googleSpreadsheetPayload = await submitGoogleSpreadsheet<MyType>(body, googleSheetsAPIKey, spreadsheetId);
    logger.info('Google spreadsheet submitted', { googleSpreadsheetPayload });
  } catch (error) {
    logger.error('Google spreadsheet error', error);
    return send(500, {});
  }

Security Tips

Never commit the Service Account JSON object to the file in Git.
Only expose as envs the values that you need.
Use .gitignore, secret managers, or environment variables.
Limit the spreadsheet access by not granting Drive-wide permissions.

Final Thoughts

This approach gives you full control of the Google Sheets API using standard Node.js, with no SDKs and no OAuth pop-up. It’s ideal for back-end scripts, internal dashboards, and server-to-server data automation.