google_apps_script.js

// YOU CAN CHANGE THE NAME OF YOUR
// DATA COLLECTION SHEET HERE ↙
const SHEET_NAME = "responses";

// Globals.
let document_properties;

const expected_hosts = [
	"/template/1/embed/",
	"flourish-user-templates.com",
	"flourish-api.com",
	"flo.uri.sh",
];

// Helpers.
function checkValue(value) {
	return !value || value === "" || value === "undefined" ? null : value; // [3]
}

function getChartID() {
	document_properties = PropertiesService.getDocumentProperties();
	return document_properties.getProperty("CHART_ID");
}

function setChartID(value) {
	document_properties = PropertiesService.getDocumentProperties();
	document_properties.setProperty("CHART_ID", value);
}

// `doPost` workflow and functions.
function writeToSheet(data) {
	// Select or create a sheet with `SHEET_NAME`.
	const doc = SpreadsheetApp.getActiveSpreadsheet();
	const sheet = doc.getSheetByName(SHEET_NAME) || doc.insertSheet(SHEET_NAME);

	// First column will always be a timestamp [1].
	sheet.getRange("A1").setValue("timestamp");
	const row = [new Date()];

	// Set up the header and variables we'll fill with data.
	const oldHeader = sheet
    .getRange(1, 1, 1, sheet.getLastColumn())
    .getValues()[0];

	const newHeader = oldHeader.slice();
	const variables = Object.keys(data);

	// Loop through the header columns.
	for (let i = 1; i < oldHeader.length; i++) {
		// Start at 1 to avoid Timestamp column
		const key = oldHeader[i];
		const output = data[key];
		row.push(output);

		// Mark as stored by removing from variables.
		const variableIndex = variables.indexOf(key);
		if (variableIndex > -1) {
			variables.splice(variableIndex, 1);
		}
	}

	// Set any new keys in our data.
	for (let i = 0; i < variables.length; i++) {
		const key = variables[i];
		const output = data[key];
		row.push(output);
		newHeader.push(key);
	}

	// Set row values.
	const nextRow = sheet.getLastRow() + 1; // get next row
	sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

	// Update header row with any new variables.
	if (newHeader.length > oldHeader.length) {
		sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
	}
}

function writeToDebug(value) {
	const doc = SpreadsheetApp.getActiveSpreadsheet();
	const debugSheet = doc.getSheetByName("debug") || doc.insertSheet("debug");

	if (debugSheet.getRange("A1").isBlank()) {
		debugSheet.getRange("A1").setValue("timestamp");
		debugSheet.getRange("B1").setValue("info");
		debugSheet
      .getRange("A1")
      .setNote(
      	`This sheet logs some additional info about the workings, which might be of interest. It can simply be deleted if not. 🫠`
      );
	}

	const next = debugSheet.getLastRow() + 1;
	debugSheet.getRange(next, 1).setValue(new Date());
	debugSheet.getRange(next, 2).setValue(value);
}

function validate(sheetData, metaData) {
	const chart_id = getChartID();

	// Always check for data.
	if (!Object.entries(sheetData).filter((d) => d[1]).length) {
		writeToDebug("No data to show");
		return false;
	}

	// Always Check for hostname.
	if (!expected_hosts.includes(metaData.host)) {
		writeToDebug("From basic check: host is unknown");
		return false;
	}

	// Check for host, pathname and chart_id if chart_id is given (only for published vises).
	if (chart_id && chart_id !== "null" && chart_id !== "") {
		const expected_host = "flo.uri.sh";
		const expected_pathname = `/visualisation/${chart_id}/embed`;

		if (
			metaData.host !== expected_host ||
      metaData.pathname !== expected_pathname
		) {
			writeToDebug(
				`From chart ID check: hostname ${metaData.host} or pathname ${metaData.pathname} is unknown`
			);
			return false;
		}
	}

	return true;
}

function prepData(data) {
	// Separate meta data from actual data.
	const metaCols = ["host", "pathname"];
	const metaData = {};
	const sheetData = {};
	Object.keys(data).forEach((key) => {
		metaCols.includes(key)
			? (metaData[key] = checkValue(data[key]))
			: (sheetData[key] = checkValue(data[key]));
	});

	// Data key order of sent data isn't guaranteed so we
	// order the data keys here for aesthetics and consistency.
	const sheetDataOrdered = Object.keys(sheetData)
    .sort()
    .reduce((obj, key) => {
    	obj[key] = sheetData[key];
    	return obj;
    }, {});

	return { metaData, sheetData: sheetDataOrdered };
}

function main(e) {
	// Hold off up to 10 sec to avoid concurrent writing.
	const lock = LockService.getDocumentLock();
	lock.waitLock(10000);

	try {
		const { sheetData, metaData } = prepData(e.parameter); // [2]
		if (!validate(sheetData, metaData)) return;
		writeToSheet(sheetData);
	}
	catch (error) {
		Logger.log(error);
	}
	finally {
		// Unlock the concurrent lock.
		lock.releaseLock();
		return;
	}
}

function doPost(e) {
	try {
		Logger.log(e);
		writeToDebug(e);
		main(e);

		return ContentService.createTextOutput(
			JSON.stringify({ result: "success", data: JSON.stringify(e.parameters) })
		).setMimeType(ContentService.MimeType.TEXT);
	}
	catch (error) {
		Logger.log(error);
		return ContentService.createTextOutput(
			JSON.stringify({ result: "error", error: error })
		).setMimeType(ContentService.MimeType.TEXT);
	}
}

// `onOpen` workflow and functions.
function editChartID() {
	// Get access to the UI class.
	const ui = SpreadsheetApp.getUi();

	// Get the current document-tied chart ID value.
	let chart_id = getChartID();

	// Set up UI to edit the chart ID.
	let result;
	if (chart_id) {
		result = ui.prompt(
			"Edit chart ID",
			`Your current chart ID is ${chart_id}. Update here:`,
			ui.ButtonSet.OK_CANCEL
		);
	}
	else {
		result = ui.prompt(
			"Edit chart ID",
			`You have no chart ID set. Set one here:`,
			ui.ButtonSet.OK_CANCEL
		);
	}

	// Process the user's response.
	const button = result.getSelectedButton();
	const id = result.getResponseText();

	// User clicked "OK".
	if (button === ui.Button.OK) {
		// Moving through the options...
		if (!id && chart_id) {
			ui.alert(
				"Info",
				`No change, you're chart ID remains ${chart_id}`,
				ui.ButtonSet.OK
			);
		}
		else if (!id && !chart_id) {
			ui.alert("Info", `No chart ID set`, ui.ButtonSet.OK);
		}
		else if (id && !chart_id) {
			setChartID(id);
			chart_id = getChartID();
			ui.alert(
				"All good",
				`We've set your chart ID to ${chart_id}`,
				ui.ButtonSet.OK
			);
		}
		else if (id && chart_id) {
			const previous_chart_id = chart_id;
			setChartID(id);
			chart_id = getChartID();
			ui.alert(
				"Done!",
				`We've changed your chart ID from ${previous_chart_id} to ${chart_id}`,
				ui.ButtonSet.OK
			);
		}
	}
}

function removeChartID() {
	// Get the current document-tied chart ID value.
	const chart_id = getChartID();
	setChartID("");

	const ui = SpreadsheetApp.getUi();
	const result = ui.alert(
		"Safety check",
		"Just checking if you're sure?",
		ui.ButtonSet.YES_NO
	);

	if (result == ui.Button.YES) {
		// Inform the user.
		ui.alert(
			"Done",
			`We've removed your chart ID ${chart_id} 🏌️‍♀️`,
			ui.ButtonSet.OK
		);
	}
}

function showInfo() {
	// Get the current document-tied chart ID value.
	const chart_id = getChartID();

	// Set the chart ID part based on chart_id existance.
	const chart_id_text = chart_id
		? `You currently have your chart ID set to <b>${chart_id}</b>.`
		: `You currently have no chart ID set.`;

	// Set the Modal text.
	const html = HtmlService.createHtmlOutput(`
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=Source+Sans+Pro:wght@400;700&display=swap" rel="stylesheet">
    <style>
      #chart-id-info { 
        font-family: 'Source Sans Pro', Arial, sans-serif;
        line-height: 1.4;
      }
    </style>
    <div id="chart-id-info">
      <p>
        For published Flourish charts you can add an additional 
        security check making sure data comes from your published 
        chart only. 
      </p>
      <p>
        For this to work, just take a note of your published <b>chart ID</b> 
        you can find in your public visualization's URL
      </p>
        <img src="https://public.flourish.studio/uploads/932/d704d2c5-9841-4749-b1ac-b571155a9e3e.jpg" width="100%"/>
      <p>
        and add or change it via the <b>Edit chart ID</b> or remove it via 
        the <b>Remove chart ID</b> menu field on this sheet's <b>Flourish</b> 
        dropdown.
      </p>
      </p>
      <p>${chart_id_text}</p> 
      <sub>
        <i>Note, this is not required, it's just an additional safety measure.</i>
      </sub>
    </div>
  `);

	// Add modal.
	SpreadsheetApp.getUi().showModalDialog(html, "Info");
}

function onOpen() {
	SpreadsheetApp.getUi()
    .createMenu("Flourish")
    .addItem("Info", "showInfo")
    .addItem("Edit chart ID", "editChartID")
    .addItem("Remove chart ID", "removeChartID")
    .addToUi();
}

/* Notes:

  [1] if another column name is set in A1, it will be overwritten.
      However, if that overwritten column is coming through with
      the sent data, it will be appended as a newHeader column due
      to how newHeader checks oldHeader as to what to add.
  [2] we're not expecting multiple URL parameters as shown here:
      https://stackoverflow.com/a/34889312/3219033
  [3] the code should run when the password arg is undefined, which
      can get stringified, so also testing for "undefined" here.

*/