https://joshuatz.com/posts/2021/google-sheets-faster-data-export-options

google-sheets-data-export-with-gas.js

/**
 * @file Mini-API to export data from a Google Sheet, created for fun
 * @author Joshua Tzucker
 * @see https://joshuatz.com/posts/2021/google-sheets-faster-data-export-options
 * @license MIT
 * @see https://gist.github.com/ronaldsmartin/47f5239ab1834c47088e (alternative)
 */

// @ts-check
/// <reference path="/yarn-global/@types/google-apps-script/index.d.ts" />


/**
 * ==== Script Constants - Configurable ====
 */
const AUTH_KEY_RANGE_NAME = 'AuthKey';

/**
 * ===== TS Helper Stuff =====
 */

/** @typedef {['csv', 'tsv', 'xlsx', 'ods', 'zip']} NativeExportFormatTuple */
/** @typedef {NativeExportFormatTuple[number]} NativeExportFormat */

/** @typedef {['csv', 'tsv', 'json']} AllowedRowExportFormatTuple */
/** @typedef {AllowedRowExportFormatTuple[number]} AllowedRowExportFormat */

/** @type {AllowedRowExportFormatTuple & string[]} */
const ALLOWED_ROW_EXPORT_FORMATS = ['csv', 'tsv', 'json'];

/** @type {NativeExportFormatTuple & string[]} */
const ALLOWED_NATIVE_EXPORT_FORMATS = ['csv', 'tsv', 'xlsx', 'ods', 'zip'];

/**
 * @param {string} format
 * @returns {format is AllowedRowExportFormat}
 */
function isAllowedRowExportFormat(format) {
	return ALLOWED_ROW_EXPORT_FORMATS.includes(format);
}

/**
 * @param {string} format
 * @returns {format is NativeExportFormat}
 */
function isAllowedNativeExportFormat(format) {
	return ALLOWED_NATIVE_EXPORT_FORMATS.includes(format);
}

/**
 * Export the current (Spreadsheet) file, in a desired format
 * - Some formats (csv, tsv) can only export a specific sheet a time (thus, `sheetGid`)
 * - Relevant: https://stackoverflow.com/a/60777806/11447682
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} doc
 * @param {NativeExportFormat} [format] - Export format
 * @param {number} [sheetGid] - Used if the format only supports single sheet export a time
 */
function getAuthedExport(doc, format = 'csv', sheetGid = 0) {
	const ssId = doc.getId();
	const csvReqUrl = `https://docs.google.com/spreadsheets/d/${ssId}/export?format=${format}&id=${ssId}&gid=${sheetGid}`;
	const token = ScriptApp.getOAuthToken();
	const response = UrlFetchApp.fetch(csvReqUrl, {
		headers: {
			Authorization: `Bearer ${token}`
		}
	});
	return response;
}

/**
 * Get the last {x} rows of a sheet, in a stringified response format
 * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
 * @param {number} [numRows] - How many rows to get data for
 * @param {AllowedRowExportFormat} [format] - Return format
 * @returns {string}
 */
function getLastRows(sheet, numRows = 1, format = 'csv') {
	const dataRange = sheet.getDataRange();
	const lastRowsRange = sheet.getRange(dataRange.getLastRow(), dataRange.getColumn(), numRows, dataRange.getNumColumns());
	let mdDataArr = lastRowsRange.getValues();

	if (format === 'csv' || format === 'tsv') {
		const delimiter = format === 'csv' ? `,` : `\t`;
		// CSV requires some special escaping
		if (format === 'csv') {
			mdDataArr = mdDataArr.map((arr) => {
				return arr.map((val) => {
					// If it contains a quote, you have to double escape
					val = val.replace(/"/gm, `""`);
					// Wrap entire string (this will also escape commas)
					val = `"${val}"`;
					return val;
				});
			});
		}
		const stringified = mdDataArr.map((r) => r.join(delimiter)).join('\n');
		return stringified;
	} else {
		return JSON.stringify(mdDataArr, null, 2);
	}
}

/**
 * Generate ContentService output to return to GET or POST
 * @param {string} content
 * @param {AllowedRowExportFormat | NativeExportFormat} format
 */
function generateContentOut(content, format) {
	const output = ContentService.createTextOutput(content);
	if (format === 'csv') {
		output.setMimeType(ContentService.MimeType.CSV);
	} else if (format === 'tsv') {
		// GAS doesn't allow custom Mime types :(
		output.setMimeType(ContentService.MimeType.CSV);
	} else if (format === 'json') {
		output.setMimeType(ContentService.MimeType.JSON);
	} else {
		output.downloadAsFile(`export.${format}`);
	}

	return output;
}

/**
 *
 * @param {GoogleAppsScript.Spreadsheet.Spreadsheet} doc
 */
function getDocAuthKey(doc) {
	const foundKeyRange = doc.getRangeByName(AUTH_KEY_RANGE_NAME);
	const foundKey = foundKeyRange ? foundKeyRange.getValue() : undefined;
	if (!!foundKey && typeof foundKey === 'string') {
		return foundKey;
	}

	return undefined;
}

/**
 * Actual API / Endpoint
 */

/**
 * GAS doesn't actually support returning non-200 status codes :(
 * This just returns error text
 * @param {string} errStr
 */
function sendError(errStr) {
	return ContentService.createTextOutput(errStr);
}

/**
 * Respond to GET requests with flexible data response
 * @param {GoogleAppsScript.Events.DoGet} e
 */
function doGet(e) {
	/** @type {Record<string, string | undefined>} */
	// prettier-ignore
	const params = (e.parameter || {action: undefined});
	let { id: requestedId, action, format, sheetName, authKey } = params;

	// If this script is attached directly to a sheet, we can use getActiveSpreadsheet(), else, the spreadsheet ID should have been passed as param
	const doc = !!requestedId ? SpreadsheetApp.openById(requestedId) : SpreadsheetApp.getActiveSpreadsheet();

	if (!doc) {
		return;
	}

	const docAuthKey = getDocAuthKey(doc);
	if (docAuthKey) {
		if (docAuthKey !== authKey) {
			return sendError('INVALID AUTH KEY');
		}
	}

	let sheet;
	if (sheetName) {
		sheet = doc.getSheetByName(sheetName);
	}
	if (!sheet) {
		// Assume first sheet in doc
		sheet = doc.getSheets()[0];
	}

	if (action === 'getLastRows') {
		let numRows = parseInt(params['numRows'], 10);
		numRows = Number.isNaN(numRows) ? 1 : numRows;
		if (!isAllowedRowExportFormat(format)) {
			return sendError(`${format} is not of an accepted format type`);
		}

		const csvText = getLastRows(sheet, numRows, format);
		return generateContentOut(csvText, format);
	}

	if (action === 'export') {
		if (!isAllowedNativeExportFormat(format)) {
			return sendError(`${format} is not of an accepted format type`);
		}

		const exportRawContent = getAuthedExport(doc, format, sheet.getSheetId());
		// This is probably only going to work for non-binary formats
		return generateContentOut(exportRawContent.getContentText(), format);
	}
}