Post data to a Google Spreadsheet

Google Spreadsheet Post

/**
 * @see http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
 * @see https://gist.github.com/willpatera/ee41ae374d3c9839c2d6
 */

// Sheet name
const SHEET_NAME = "Submissions";

// Property service
const ScriptProps = PropertiesService.getScriptProperties();

/**
 * Get spreadsheet
 *
 * @return Spreadsheet
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
let spreadsheet = null;
const getSpreadsheet = () => {
    if (!spreadsheet) {
        spreadsheet = SpreadsheetApp.openById(
            ScriptProps.getProperty("sheet_id")
        );
    }
    return spreadsheet;
};

/**
 * Get sheet
 *
 * @return Sheet
 * @see https://developers.google.com/apps-script/reference/spreadsheet/sheet
 */
let sheet = null;
const getSheet = () => {
    if (!sheet) {
        sheet = getSpreadsheet().getSheetByName(SHEET_NAME);
    }
    return sheet;
};

/**
 * Get header values
 *
 * @return Array
 */
const getHeaderValues = () =>
    getSheet().getRange(1, 1, 1, getSheet().getLastColumn()).getValues()[0];

/**
 * Parse post data
 */
const getRequestData = (request) => {
    const { parameter, postData: { contents, type } = {} } = request;

    switch (type) {
        case "application/json":
            return JSON.parse(contents);
        case "application/x-www-form-urlencoded":
            let data = {};
            contents
                .split("&")
                .map((input) => input.split("="))
                .forEach(([key, value]) => {
                    data[decodeURIComponent(key)] = decodeURIComponent(value);
                });
            return data;
        default:
            return parameter;
    }
};

/**
 * Handle response
 *
 * Shortly after my original solution Google announced the LockService
 * to prevent concurrent access overwritting data
 * @see http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
 */
const handleResponse = (request) => {
    // Create lock and set for 30 seconds
    const lock = LockService.getPublicLock();
    lock.waitLock(30 * 1000);

    try {
        // Get header values
        const headers = getHeaderValues();

        // Get request data
        const data = getRequestData(request);

        // Get row number for new row
        const nextRow = getSheet().getLastRow() + 1;

        // Map values
        const values = headers.map((key) =>
            key == "date" ? new Date() : data[key] || ""
        );

        // Write values to row
        getSheet().getRange(nextRow, 1, 1, values.length).setValues([values]);

        // Success
        return ContentService.createTextOutput(
            JSON.stringify({ result: "success", row: nextRow })
        ).setMimeType(ContentService.MimeType.JSON);
    } catch (error) {
        // Error
        return ContentService.createTextOutput(
            JSON.stringify({ result: "error", error })
        ).setMimeType(ContentService.MimeType.JSON);
    } finally {
        // Release lock
        lock.releaseLock();
    }
};

/**
 * GET request
 */
const doGet = handleResponse;

/**
 * POST request
 */
const doPost = handleResponse;

/**
 * Setup
 */
const setup = () =>
    ScriptProps.setProperty(
        "sheet_id",
        SpreadsheetApp.getActiveSpreadsheet().getId()
    );