Import from Google Sheets

script

/* ✨ IMPORT FROM GOOGLE SHEETS ✨ */
/* An action for Drafts, written by Tyler Robertson (@aTylerRobertson on Twitter) */

// Build the initial prompt
const p = Prompt.create();
p.title = "Import from Google Sheets";
p.message = `Paste the URL of a Google Sheets spreadsheet that has 'Anyone with the link can view' permissions.\n\nThen, enter the name of the specific worksheet that you want to import (i.e. 'Sheet1').`;
p.addTextField("url", "URL", "");
p.addTextField("sheet","Worksheet", "Sheet1");
p.addSwitch("checkboxes","Convert TRUE/FALSE values to checkboxes",false);
p.addButton("Import");

if (p.show() && p.fieldValues["url"] != "") {
	// Parse the user's input
	const id = p.fieldValues["url"].match(/\/d\/(.*)\//)[1];
	const sheet = p.fieldValues["sheet"];
	
	// Make an HTTP request to Google Sheets
	const http = HTTP.create();
	let response = http.request({
		"url": `https://docs.google.com/spreadsheets/d/${id}/gviz/tq?tqx=out:json&sheet=${sheet.replace(/\s/g,'+')}`,
		"method": "GET",
	});

	// On a successful response, parse the sheet and add it to the draft
	if (response.success) {
		const text = response.responseText;
		// Remove unnecessary text from the response
		const json = JSON.parse(text.substr(47).slice(0, -2));
		
		// Ignore empty rows & columns
		const cols = json.table.cols.filter(c => c.label !== '');
		const rows = json.table.rows.filter(r => r);

		draft.content += '\n|';
		// The first row is listed in the "cols" value
		for (const col of cols) {
			let cell = col.label;
			// Replace TRUE/FALSE with checkboxes
			if (p.fieldValues["checkboxes"]) {
				if (cell == true) cell = '[x]';
				if (cell == false) cell = '[ ]';
			}
			draft.content += ` ${cell}${String(cell).length < 6 ? '\t\t' : '\t'}|`;
		}
		// Add alignment after first row
		draft.content += '\n|';
		for (const col of cols) {
			draft.content += ` :---\t\t|`;
		}
		// Iterate through remaining rows
		for (const row of rows) {
			draft.content += '\n|';
			for (const col of row.c) {
				let cell = col.v;

				// Replace TRUE/FALSE with checkboxes
				if (p.fieldValues["checkboxes"]) {
					if (cell == true) cell = '[x]';
					if (cell == false) cell = '[ ]';
				}

				draft.content += ` ${cell}${String(cell).length < 6 ? '\t\t' : '\t'}|`;
			}
		}
	} else {
		console.log(response.error);
	}
}