NodeJS demonstration of Google's new (July, 2026) internal API for exporting / downloading Google Sheets as PDFs programmatically. Server-side, no Apps Script. Headless and automatable.

gsheetpdf.js

/* 
   gsheetpdf.js 
   
   Author: Daniel Bromberg daniel@basezen.com Copyright 2026
   License: MIT https://opensource.org/license/mit

   Part of Gist: Demonstration of Google's new (July, 2026) internal API for downloading Google Sheets as PDFs
   programmatically.

   DISCUSSION: Rather than the /export endpoint, which now generates 500 (for my use case),
   Google's own UI uses a new /pdf endpoint with a much more elaborate,
   generalized JSON interface, with a POST to:

   https://docs.google.com/spreadsheets/u/2/d/[WORKSHEET-ID]/pdf?id=[WORKSHEET-ID]&esid=[RANDOM_NONCE]
   and an embedded JSON object in the form data named 'pc'
   
   Advantages:
   -- Uses what Google's own PDF generation is now using, indicating the old method (/export) may be getting phased out
   -- Has more nuanced control over document generation, which can only improve as more is reverse-engineered.

   Disadvantages:
   -- This method appears to work only with Google Dev service accounts, and as such is automatable/headless but not interactive:
 	-- no OAuth / browser interaction
	-- no App Scripts integration
   
   INSTALL: npm install google-auth-library googleapis
   
   AUTHENTICATION: 
   1. You need a sufficiently robust bearer token.
     -- According to Tanaike's investigation, an ordinary OAuth bearer token does not work and generates a 400 error
     -- Verified to work so far is a AWS-federated workload credential connected to a google service worker that has been
        granted full read/write priveleges on the document in question
   
   CONFIGURATION:
   2. You will need to fill in the workbook ID and the worksheet ID and the desired row and column maximums if desired.
   3. You should customize the generation parameters if you see fit in the call to pdf_export_data

   AFTER YOU DECLARE VICTORY:
   4. You should give me feedback to make this more robust and helpful.

   DISCUSSION
   I use Google Federated service workers trusted through an AWS endpoint to create an authenticated
   pathway into the Google API. Requires significant configuration in the Google Developer console,
   and in this case AWS coordination.  It all boils down to generating a session token, name 'Bearer'
   and value takes form of a 1024-char base64-encoded token ending in ': authorization'

   This method assumes existence of environment variables process.env.NODE_HOME and
   process.env.GOOGLE_CREDENTIAL_SUBPATH, which when joined specify the path to the credentials JSON
   file. See gcredfed.json
*/
'use strict';

/* Endpoint configuration */
const GOOGLE_DEFAULTS = {
    host: 'docs.google.com',
    path: 'spreadsheets/d',
    method: 'pdf'
};

/* User configuration. Fill in with your own values */
const google_dev_project_id = '000000000000';
const google_workbook_id = 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF';
const google_worksheet_id = '1111111111';

/* Standard Libraries */
import { randomBytes } from 'node:crypto';
import { join } from 'node:path';
import { Readable } from 'node:stream';
import { GoogleAuth } from 'google-auth-library';
import { google } from 'googleapis';
import { open } from 'node:fs/promises';

/* Helpers */
const random_key = length => randomBytes(length).toString('hex');


/* Excel, and by forced compatibility, Google Sheets, encode time in a deeply baroque way */
const unix_local_now = d => d.getTime() / 1000.0 - d.getTimezoneOffset() * 60;
// +2 offset: 1 for the 1-based nature (Jan 1, 1900 is day 1, not 0; and 1 for the legacy, fake Leap Year 1900/02/29
const unix_epoch_to_1900_epoch = epoch => (epoch + 2208988800) / 86400.0 + 2;
const spreadsheet_local_now = () => unix_epoch_to_1900_epoch(unix_local_now(new Date()));

const stream_to_file = (stream, fh) => new Promise((resolve, reject) => {
    stream.on('data', chunk => fh.write(chunk));
    stream.on('end', () => fh.stat().then(stats => fh.close().then(() => resolve(stats))));
    stream.on('error', err => reject(err));
});

/* Reverse engineered API */
const pdf_export_url = ( sheet_id, opts = GOOGLE_DEFAULTS ) => 
      `https://${opts.host}/${opts.path}/${sheet_id}/${opts.method}?id=${sheet_id}&esid=${random_key(8)}`;

const pdf_export_data = ( tab_id, 
			  top_row, bottom_row,
			  left_col, right_col, 
			  spreadsheet_day, 
			  show_notes = 0,
			  hide_gridlines = 0,
			  show_page_numbers = 0,
			  show_workbook_title = 1,
			  show_sheet_name = 1,
			  show_current_date = 1,
			  show_current_time = 1,
			  horizontal_alignment = 2, /* 1 = left, 2 = center, 3 = right */
			  vertical_alignment = 1, /* 1 = top, 2 = center, 3 = bottom */
			  multi_page_ordering = 2, /* 1 = down then over; 2 = over then down */
			  use_custom_header_footers = 2, /* 1 = no, 2 = yes */
			  /* 
			     0-9: Unused
			     \uee10: Workbook Name
			     \uee11: Sheet Name
			     \uee12: Page Number (just the integer)
			     \uee13\uee14: Date
			     \uee15: Page Number (preceeded by "Page ")
			     \uee16: Page Number (preceeded by "Pg ")
			     \uee17\uee18: Time
			     \uee19: Time and Date
			     20-24: Unused 
			  */
			  headers = [["\uee10 TOPLEFT"], ["\uee11 TOPMIDDLE"], ["TOPRIGHT \uee12"]],
			  footers = [["\uee17\uee18 BOTLEFT"], ["BOTMIDDLE \uee10"], ["BOTRIGHT \uee11"]],
			  scale_mode = 4, /* 1 = Normal; 2 = Fit to width; 3 = Fit to Height; 4 = Fit to Page; 5 = Custom */
			  custom_scale = 1.0,
			  page_size = 'letter',
			  orientation = 1, /* 0 = landscape; 1 = portrait */
			  margin_top = 0.75, margin_bottom = 0.75, margin_left = 0.25, margin_right = 0.25 ) => ({
			      a: true,
			      pc: [
				  null, null, null, null, null, null, null, null, null,
				  0, 
				  [ [tab_id, top_row, bottom_row, left_col, right_col ], /* [ tab_id_2, 0, 2, 0, 2] ] works */,
				  10000000,
				  null, null, null, null, null, null, null, null, null, null, null, null, null, null,
				  spreadsheet_day,
				  null, null,
				  [
				      show_notes, 
				      null, 
				      hide_gridlines, 
				      show_page_numbers,
				      show_workbook_title,
				      show_sheet_name,
				      show_current_date,
				      show_current_time,
				      1,
				      1,
				      multi_page_ordering,
				      use_custom_header_footers,
				      headers,
				      footers,
				      horizontal_alignment,
				      vertical_alignment],
				  [page_size, orientation, scale_mode, custom_scale, [margin_top, margin_bottom, margin_left, margin_right]], null, 0
			      ],
			      gf: [],
			      lds: []
			  });


const form_encode_json_values = fields =>
      new URLSearchParams(Object.entries(fields).map(([name, value]) => [ name, JSON.stringify(value) ]));


/* Authentication bootstrapping. Note my assumptions */
const google_init = project_id => new Promise((resolve, reject) => {
    if ( !process.env.HOME || !process.env.GOOGLE_CREDENTIAL_SUBPATH ) {
	throw new Error('Credential environment missing: ${process.env.NODE_HOME} ${process.env.GOOGLE_CREDENTIAL_SUBPATH');
    }
    process.env.GOOGLE_APPLICATION_CREDENTIALS = join(process.env.NODE_HOME, process.env.GOOGLE_CREDENTIAL_SUBPATH);
    const auth = new GoogleAuth({
	scopes: [ 
	    'https://www.googleapis.com/auth/cloud-platform', 
	    'https://www.googleapis.com/auth/drive.readonly' 
	],
	projectId: project_id
    });

    resolve(google.drive({version: 'v3', auth}));
});


const sheet_tab_pdf_stream = (provider, url, worksheet_id, max_row, max_col) =>
      provider.context._options.auth.getRequestHeaders(url)
      .then(auth_headers => new Request(url, { 
	  headers: new Headers([ ...auth_headers, [ 'content-type', 'application/x-www-form-urlencoded' ]]),
	    method: 'POST',
	    body: form_encode_json_values(pdf_export_data(worksheet_id, 0, max_row, 0, max_col, spreadsheet_local_now()))
	}))
	.then(fetch)
	.then(response => response.ok
	      ? Readable.fromWeb(response.body)
	      : response.text().then(error_output => Promise.reject({ response, error_output })))
	.catch(failure => 
	    Promise.reject(failure.response && failure.error_output ? 
			   new Error(`Failed PDF stream! status: ${failure.response.status} ${failure.response.statusText}`
				     + ` '${failure.error_output.substring(0, 4096)}'`)
			   : failure))


console.log(`URL: ${pdf_export_url(google_workbook_id)}`);

google_init(google_dev_project_id)
    .then(gsheet_provider =>
	sheet_tab_pdf_stream(gsheet_provider, pdf_export_url(google_workbook_id), google_worksheet_id, 60, 4)
	    .then(stream => open('out.pdf', 'w').then(fh => stream_to_file(stream, fh)))
	    .then(file_stats => console.log(`success: ${JSON.stringify(file_stats)}`)));

/* 
Example Server-Based Credentials File (anonymized; this is a generated file, for context)
{
    "universe_domain": "googleapis.com",
    "type": "external_account",
    "audience": "//iam.googleapis.com/projects/000000000000/locations/global/workloadIdentityPools/job-reporting-pool/providers/provider-name",
    "subject_token_type": "urn:ietf:params:aws:token-type:aws4_request",
    "service_account_impersonation_url": "https://iamcredentials.googleapis.com/v1/projects/-/serviceAccounts/service-account@workload-federation-9999999.iam.gserviceaccount.com:generateAccessToken",
    "token_url": "https://sts.googleapis.com/v1/token",
    "credential_source": {
	"environment_id": "aws1",
	"region_url": "http://169.254.169.254/latest/meta-data/placement/availability-zone",
	"url": "http://169.254.169.254/latest/meta-data/iam/security-credentials",
	"regional_cred_verification_url": "https://sts.{region}.amazonaws.com?Action=GetCallerIdentity&Version=2011-06-15"
    }
}
*/