Skip to content

Instantly share code, notes, and snippets.

@thealchen

thealchen/coda_google_sheets_one_way_sync.js

Last active November 7, 2022 03:30
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save thealchen/ec44e1aae95730b827e6b58a7ec9a317 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save thealchen/ec44e1aae95730b827e6b58a7ec9a317 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. thealchen revised this gist Jul 16, 2020. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions coda_google_sheets_one_way_sync.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    // One-way data sync from Coda to Google Sheets using Google Apps Script
    // Author: Al Chen (al@coda.io)
    // Last Updated: May 3, 2020
    // Last Updated: July 16, 2020
    // Notes: Assumes you are using the V8 runtime (https://developers.google.com/apps-script/guides/v8-runtime)
    // Coda's library for Google Apps Script: 15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl

    @@ -206,7 +206,7 @@ function retrieveRows() {
    var sourceColumns = CodaAPI.listColumns(SOURCE_DOC_ID, SOURCE_TABLE_ID).items.map(function(item) { return item.name; });

    do {
    var response = CodaAPI.listRows(SOURCE_DOC_ID, SOURCE_TABLE_ID, {limit: 500, pageToken: pageToken, useColumnNames: true});
    var response = CodaAPI.listRows(SOURCE_DOC_ID, SOURCE_TABLE_ID, {limit: 500, pageToken: pageToken, useColumnNames: true, sortBy: 'natural'});
    var sourceRows = sourceRows.concat(response.items);
    pageToken = response.nextPageToken;
    } while (pageToken);
  2. thealchen revised this gist May 3, 2020. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions coda_google_sheets_one_way_sync.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    // One-way data sync from Coda to Google Sheets using Google Apps Script
    // Author: Al Chen (al@coda.io)
    // Last Updated: April 28, 2020
    // Last Updated: May 3, 2020
    // Notes: Assumes you are using the V8 runtime (https://developers.google.com/apps-script/guides/v8-runtime)
    // Coda's library for Google Apps Script: 15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl

    @@ -237,12 +237,12 @@ function printDocTables() {

    // Sort function for array of arrays which sorts Google Sheets rows by SOURCE_SHEET_SOURCE_ROW_COLUMN in alphabetical order
    function sortArray(a, b) {
    var upperA = a[rowURLIndex].toUpperCase();
    var upperB = b[rowURLIndex].toUpperCase();
    if (upperA === upperB) {
    var x = a[rowURLIndex];
    var y = b[rowURLIndex];
    if (x === y) {
    return 0;
    }
    else {
    return (upperA < upperB) ? -1 : 1;
    return (x < y) ? -1 : 1;
    }
    }
  3. thealchen revised this gist May 1, 2020. 1 changed file with 7 additions and 4 deletions.
    11 changes: 7 additions & 4 deletions coda_google_sheets_one_way_sync.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,8 @@
    // One-way data sync from Coda to Google Sheets using Google Apps Script
    // Author: Al Chen (al@coda.io)
    // Last update: March 9th, 2020
    // Last Updated: April 28, 2020
    // Notes: Assumes you are using the V8 runtime (https://developers.google.com/apps-script/guides/v8-runtime)
    // Coda's library for Google Apps Script: 15IQuWOk8MqT50FDWomh57UqWGH23gjsWVWYFms3ton6L-UHmefYHS9Vl

    //////////////// Setup and global variables ////////////////////////////////

    @@ -234,12 +235,14 @@ function printDocTables() {
    Logger.log('Tables are: ' + prettyPrint(tables));
    }

    // Sort function for array of arrays
    // Sort function for array of arrays which sorts Google Sheets rows by SOURCE_SHEET_SOURCE_ROW_COLUMN in alphabetical order
    function sortArray(a, b) {
    if (a[rowURLIndex] === b[rowURLIndex]) {
    var upperA = a[rowURLIndex].toUpperCase();
    var upperB = b[rowURLIndex].toUpperCase();
    if (upperA === upperB) {
    return 0;
    }
    else {
    return (a[rowURLIndex] < b[rowURLIndex]) ? -1 : 1;
    return (upperA < upperB) ? -1 : 1;
    }
    }
  4. thealchen revised this gist Mar 9, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion coda_google_sheets_one_way_sync.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    // One-way data sync from Coda to Google Sheets
    // One-way data sync from Coda to Google Sheets using Google Apps Script
    // Author: Al Chen (al@coda.io)
    // Last update: March 9th, 2020
    // Notes: Assumes you are using the V8 runtime (https://developers.google.com/apps-script/guides/v8-runtime)
  5. thealchen revised this gist Mar 9, 2020. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions coda_google_sheets_one_way_sync.js
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,6 @@
    // One-way data sync from Coda to Google Sheets
    // Author: Al Chen (al@coda.io)
    // Last update: March 9th, 2020
    // Notes: Assumes you are using the V8 runtime (https://developers.google.com/apps-script/guides/v8-runtime)

    //////////////// Setup and global variables ////////////////////////////////
  6. thealchen created this gist Mar 9, 2020.
    244 changes: 244 additions & 0 deletions coda_google_sheets_one_way_sync.js
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,244 @@
    // One-way data sync from Coda to Google Sheets
    // Author: Al Chen (al@coda.io)
    // Notes: Assumes you are using the V8 runtime (https://developers.google.com/apps-script/guides/v8-runtime)

    //////////////// Setup and global variables ////////////////////////////////

    CodaAPI.authenticate('YOUR_API_KEY')
    SOURCE_DOC_ID = 'YOUR_SOURCE_DOC_ID'
    SOURCE_TABLE_ID = 'YOUR_SOURCE_TABLE_ID'
    TARGET_SHEET_ID = 'YOUR_GOOGLE_SHEETS_ID'
    TARGET_WORKSHEET_NAME = 'YOUR_GOOGLE_SHEETS_WORKSHEET_NAME'
    TARGET_SHEET_SOURCE_ROW_COLUMN = 'YOUR_SOURCE_ROW_URL_COLUMN_NAME'

    ////////////////////////////////////////////////////////////////////////////

    toSpreadsheet = SpreadsheetApp.openById(TARGET_SHEET_ID);
    toWorksheet = toSpreadsheet.getSheetByName(TARGET_WORKSHEET_NAME);
    headerRow = toWorksheet.getDataRange().offset(0, 0, 1).getValues()[0];
    rowURLIndex = headerRow.indexOf(TARGET_SHEET_SOURCE_ROW_COLUMN);

    // Run main sync functions
    function runSync() {
    addDeleteToSheet();
    updateSheet();
    }

    // Updates existing rows in Sheet if any changes in Coda table
    function updateSheet() {
    var matchingRows = [];
    var diffRowURLs = [];
    var diffRows = [];
    var allRows = prepRows();
    var sourceRows = allRows['sourceRows'];
    var targetRows = allRows['targetRows'];
    var sortedTargetRows = targetRows.sort(sortArray);
    var targetRowURLs = toWorksheet.getRange(2, rowURLIndex + 1, targetRows.length).getValues().flat();
    var editCount = 0;

    // Find rows in Coda table only if it exists in Sheets
    sourceRows.map(function(row) {
    var rowURL = row['cells'].slice(-1)[0]['value'];
    if (targetRowURLs.indexOf(rowURL) != -1) {
    matchingRows.push(row)
    }
    })
    var sortedMatchingRows = convertValues(sortCodaTableCols(matchingRows)).sort(sortArray)
    var numCols = sortedMatchingRows[0].length;

    // Create array of rows that need to be updated in Sheets
    for (var i = 0; i < sortedMatchingRows.length; i++) {
    for (var j = 0; j < numCols - 1; j++) {
    if (sortedMatchingRows[i][j] == null) {
    continue;
    }
    else if (sortedMatchingRows[i][j].length != sortedTargetRows[i][j].length) {
    if (diffRowURLs.indexOf(sortedMatchingRows[i][rowURLIndex]) == -1) { diffRowURLs.push(sortedMatchingRows[i][rowURLIndex]); }
    }
    else if (sortedMatchingRows[i][j] != sortedTargetRows[i][j]) {
    if (diffRowURLs.indexOf(sortedMatchingRows[i][rowURLIndex]) == -1) { diffRowURLs.push(sortedMatchingRows[i][rowURLIndex]); }
    }
    }

    // Get the full row from source Coda table if one of the row URLs needs updating in the Sheets file
    diffRowURLs.map(function(row) {
    if (sortedMatchingRows[i][rowURLIndex] == row) {
    diffRows.push(sortedMatchingRows[i]);
    }
    })
    }

    // Update row in Sheets
    diffRows.map(function(row) {
    diffRowIndex = targetRowURLs.indexOf(row[rowURLIndex]);
    var sheetRow = toWorksheet.getRange(diffRowIndex + 2, 1, 1, numCols).getValues().flat();
    for (var i = 0; i < numCols - 1; i++) {
    if (row[i] == null) {
    continue;
    }
    else if (row[i] != sheetRow[i]) {
    editCount++;
    toWorksheet.getRange(diffRowIndex + 2, i + 1, 1).setValue(row[i]);
    }
    }
    })
    Logger.log('::::: %s values changed in Coda => Updating "%s" in Google Sheets...', editCount, TARGET_WORKSHEET_NAME);
    editCount = 0;
    }

    // Append new data from Coda table to Sheets and delete any rows from Sheets if in Coda table
    function addDeleteToSheet() {
    var allRows = prepRows();
    if (allRows['targetRows'].length > 0) {
    var targetRowURLs = toWorksheet.getRange(2, rowURLIndex + 1, allRows['targetRows'].length).getValues().flat();
    var deletedRows = findDeletedRows(allRows['sourceRows'], targetRowURLs);
    }
    else {
    targetRowURLs = [];
    deletedRows = [];
    }
    var sourceRows = findNewRows(allRows['sourceRows'], targetRowURLs);

    // Add rows to Sheets only if new rows exist
    if (sourceRows.length != 0) {
    Logger.log('::::: Adding %s new rows from Coda => "%s" in Google Sheets...', sourceRows.length, TARGET_WORKSHEET_NAME);
    var sortedSourceRows = sortCodaTableCols(sourceRows)
    var convertedSourceRows = convertValues(sortedSourceRows);
    toWorksheet.getRange(toWorksheet.getLastRow() + 1, 1, convertedSourceRows.length, convertedSourceRows[0].length).setValues(convertedSourceRows)
    }

    // Remove deleted rows
    if (deletedRows.length != 0) {
    Logger.log('::::: %s deleted rows in Coda => Deleting these row in "%s" in Google Sheets...', deletedRows.length, TARGET_WORKSHEET_NAME);
    deletedRows.map(function(row) {
    toWorksheet.deleteRow(targetRowURLs.indexOf(row) + 2);
    })
    }
    }

    // Pre-processing step for retrieving/cleaning rows from source and target
    function prepRows() {
    var sourceRows = retrieveRows();
    var targetRows = getSheetValues();
    targetRows.shift(); // Remove header row from Sheets range
    return {sourceRows: sourceRows, targetRows: targetRows}
    }

    function findDeletedRows(sourceRows, targetRowURLs) {
    var deletedRows = [];
    var sourceRowURLs = sourceRows.map(function(row) {
    return row['cells'].slice(-1)[0]['value'];
    })
    targetRowURLs.map(function(row) {
    if (sourceRowURLs.indexOf(row) == -1) {
    deletedRows.push(row)
    }
    })
    return deletedRows;
    }

    // Finds new rows in Coda table to sync
    function findNewRows(sourceRows, targetRowURLs) {
    var newRows = [];
    sourceRows.map(function(row) {
    var rowURL = row['cells'].slice(-1)[0]['value'];
    if (targetRowURLs.indexOf(rowURL) == -1) {
    newRows.push(row)
    }
    })
    return newRows;
    }

    // Converts Coda table rows to 2D array of values for Sheets
    function convertValues(rows) {
    var values = rows.map(function(row) {
    var rowValues = []
    row['cells'].map(function(rowValue) {
    rowValues.push(rowValue['value'])
    })
    return rowValues;
    })
    return values;
    }

    // Sort's Coda's table columns by column order in Sheet
    function sortCodaTableCols(sourceRows) {
    var headerCodaTable = sourceRows[0]['cells'].map(function(row) { return row['column'] });
    var sheetsColOrder = [];

    headerRow.map(function(col) {
    sheetsColOrder.push(headerCodaTable.indexOf(col))
    })

    var sortedSourceRows = sourceRows.map(function(row) {
    var cells = sheetsColOrder.map(function(col) {
    if (col == -1) {
    return {
    column: null,
    value: null,
    }
    }
    else {
    return {
    column: headerCodaTable[col],
    value: row['cells'][col]['value'],
    }
    }
    });
    return {cells: cells}
    })
    return sortedSourceRows;
    }

    // Get values from Sheets
    function getSheetValues() {
    var values = toWorksheet.getDataRange().getValues();
    return values;
    }

    // Get all Coda table rows
    function retrieveRows() {
    var sourceTable = CodaAPI.getTable(SOURCE_DOC_ID, SOURCE_TABLE_ID);
    var sourceRows = [];
    var pageToken;
    var sourceColumns = CodaAPI.listColumns(SOURCE_DOC_ID, SOURCE_TABLE_ID).items.map(function(item) { return item.name; });

    do {
    var response = CodaAPI.listRows(SOURCE_DOC_ID, SOURCE_TABLE_ID, {limit: 500, pageToken: pageToken, useColumnNames: true});
    var sourceRows = sourceRows.concat(response.items);
    pageToken = response.nextPageToken;
    } while (pageToken);

    var upsertBodyRows = sourceRows.map(function(row) {
    var cells = sourceColumns.map(function(colName) {
    return {
    column: colName,
    value: row.values[colName],
    };
    });
    cells.push({column: TARGET_SHEET_SOURCE_ROW_COLUMN, value: row.browserLink});
    return {cells: cells};
    });
    return upsertBodyRows;
    }

    ////// Helper functions //////

    function prettyPrint(value) {
    return JSON.stringify(value, null, 2);
    }

    function printDocTables() {
    var tables = CodaAPI.listTables(SOURCE_DOC_ID).items;
    Logger.log('Tables are: ' + prettyPrint(tables));
    }

    // Sort function for array of arrays
    function sortArray(a, b) {
    if (a[rowURLIndex] === b[rowURLIndex]) {
    return 0;
    }
    else {
    return (a[rowURLIndex] < b[rowURLIndex]) ? -1 : 1;
    }
    }