Action

Import from Google Sheets

Posted by Tyler Robertson, Last update 2 months ago

Import Google Sheets spreadsheets as Markdown Tables!

Note: Sheet must have “Anyone with the link can view” permissions to work.

If you have questions or run into trouble, feel free to reach out to me on Twitter. ✨

Steps

  • 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);
    	}
    }

Options

  • After Success Default
    Notification Info
    Log Level Info
Items available in the Drafts Directory are uploaded by community members. Use appropriate caution reviewing downloaded items before use.