Sync a large table with API

Developers Central
6

@DSB, did you solve this? Since you tried 1 row at a time and still got 400, also judging from your other post, probably a single row is exceeding the request size limit?

Maybe it boils down to upserting one column at a time (even though it might still go over the limit…). Here’s a code that could work (untested):

// Setup and global variables

CodaAPI.authenticate(‘[I put my token here]’);  // Replace with your token.


// Functions


SOURCE_TABLES = [
  {
    doc: ‘abcdEFG’,
    table: 'grid-H9NVeVM265', //Law School Medians
  },
  // Add more as needed.
];
TARGET_TABLE = {
  doc: 'HtSEUS1vRv', //Test Update Table
  table: 'grid-H9NVeVM265', //Law School Medians
};
TARGET_TABLE_SOURCE_ROW_COLUMN = 'Source Row URL';

/** Run me! */
function oneWaySync() {
  for each (var source in SOURCE_TABLES) {
    syncSpecificTable(source, TARGET_TABLE);
  }
}

// TODO: handle pagination for syncing source tables with >500 items.
function syncSpecificTable(source, target) {
  // Get info on the source and target tables.
  var sourceTable = CodaAPI.getTable(source.doc, source.table);
  var targetTable = CodaAPI.getTable(target.doc, target.table);
  Logger.log('::::: Syncing "%s" => "%s"...', sourceTable.name, targetTable.name);

  // Find which columns we have to sync.
  var sourceColumns = CodaAPI.listColumns(source.doc, source.table).items.map(function(item) { return item.name; });
  var targetColumns = CodaAPI.listColumns(target.doc, target.table).items.map(function(item) { return item.name; });
  var commonColumns = intersection(sourceColumns, targetColumns);
  Logger.log('Syncing columns: %s', commonColumns.join(', '));
  
  // Pull down all the rows in the source table.
  var sourceRows = CodaAPI.listRows(source.doc, source.table, {limit: 500, useColumnNames: true}).items;
  Logger.log('Source table has %s rows', sourceRows.length);

-   // Upsert all rows in the source table into the target table.
-   var upsertBodyRows = sourceRows.map(function(row) {
-     var cells = commonColumns.map(function(colName) {
-       return {
-         column: colName,
-         value: row.values[colName],
-       };
-     });
-     // Add a URL to the source row in the target, table, which will also be used as the upsert key.
-     cells.push({column: TARGET_TABLE_SOURCE_ROW_COLUMN, value: row.browserLink})
-     return {cells: cells};
-   });
- //OLEG's Updated Code:

-  CHUNK_SIZE = 100; // Adjust as needed until you don't get 400s.

-   for (var i = 0; i < upsertBodyRows.length; i += CHUNK_SIZE) {
-     var chunk = upsertBodyRows.slice(i, i + CHUNK_SIZE);
-     CodaAPI.upsertRows(target.doc, target.table, {rows: chunk, keyColumns: [TARGET_TABLE_SOURCE_ROW_COLUMN]});
-   }
+	sourceRows.forEach(row => {
+	  commonColumns.forEach(colName => {
+	    const upsertBodyRows = [
+	        {
+	          cells: [
+	            {
+	              column: colName,
+	              value: row.values[colName]
+	            },
+	            {
+	              column: TARGET_TABLE_SOURCE_ROW_COLUMN, 
+	              value: row.browserLink
+	            }
+	          ]
+	        }
+	      ]
+	      CodaAPI.upsertRows(target.doc, target.table, {rows: upsertBodyRows, keyColumns: [TARGET_TABLE_SOURCE_ROW_COLUMN]});
+	  })
+	})
+	Logger.log('Updated %s!', targetTable.name);
}

function intersection(a, b) {
  var result = [];
  for each (var x in a) {
    if (b.indexOf(x) !== -1) {
      result.push(x);
    }
  }
  return result;
}
1 Like