@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;
}