Copy new rows to a second spreadsheet

Every hour, the key column of a source tab is compared with the key column of a destination tab in another spreadsheet, and every row whose key does not yet appear in the destination is appended there. One team keeps the list, another gets its own copy, and nobody pastes anything.

The copy is one way and append only. Nothing already in the destination is ever written over, and edits or deletions made in the source are not mirrored. A row counts as copied once its key appears in the destination, so running it twice copies nothing the second time. Rows with an empty key cell are left alone and counted in the run summary, a row that fails to copy is reported and tried again next run, and a large first copy is spread over several runs.

Both tabs need the same layout, with a column that identifies each row uniquely, such as an order number or an email address, in the same position in each. If the destination tab is empty, the source's header row is copied across first.

Run every hour.Run every hour.

Set sourceSpreadsheetLink to blank.Set sourceSpreadsheetLink to blank.

Set sourceTabName to blank.Set sourceTabName to blank.

Set destinationSpreadsheetLink to blank.Set destinationSpreadsheetLink to blank.

Set destinationTabName to blank.Set destinationTabName to blank.

Set keyColumnNumber to 1.Set keyColumnNumber to 1.

Set maxNewRowsPerRun to 200.Set maxNewRowsPerRun to 200.

Get the first item of the last item of sourceSpreadsheetLink split by /spreadsheets/d/ split by / and save it as sourceFileId.Get the first item of the last item of sourceSpreadsheetLink split by /spreadsheets/d/ split by / and save it as sourceFileId.

Get the first item of the last item of destinationSpreadsheetLink split by /spreadsheets/d/ split by / and save it as destinationFileId.Get the first item of the last item of destinationSpreadsheetLink split by /spreadsheets/d/ split by / and save it as destinationFileId.

If sourceFileId is destinationFileId and sourceTabName is destinationTabNameIf sourceFileId is destinationFileId and sourceTabName is destinationTabName

Yes

Stop and report a problem: The source and the destination are the same tab, so every run would copy the sheet into itself. Point 'destinationTabName' at a different tab.Stop and report a problem: The source and the destination are the same tab, so every run would copy the sheet into itself. Point 'destinationTabName' at a different tab.

No

No steps

Google SheetsRead column keyColumnNumber of sourceTabName in sourceSpreadsheetLink. Save it as sourceKeyColumn.Read column keyColumnNumber of sourceTabName in sourceSpreadsheetLink. Save it as sourceKeyColumn.

Set sourceKeys to sourceKeyColumn.values.Set sourceKeys to sourceKeyColumn.values.

Google SheetsRead column keyColumnNumber of destinationTabName in destinationSpreadsheetLink. Save it as destinationKeyColumn.Read column keyColumnNumber of destinationTabName in destinationSpreadsheetLink. Save it as destinationKeyColumn.

Set copiedKeys to destinationKeyColumn.values.Set copiedKeys to destinationKeyColumn.values.

If sourceKeys is emptyIf sourceKeys is empty

Yes

Finish the run: The source tab has nothing in its key column, so there was nothing to copy.Finish the run: The source tab has nothing in its key column, so there was nothing to copy.

No

No steps

If copiedKeys is emptyIf copiedKeys is empty

Yes

Google SheetsRead row 1 of sourceTabName in sourceSpreadsheetLink. Save it as sourceHeaderRow.Read row 1 of sourceTabName in sourceSpreadsheetLink. Save it as sourceHeaderRow.

Set headerValues to sourceHeaderRow.values.Set headerValues to sourceHeaderRow.values.

If headerValues isn't emptyIf headerValues isn't empty

Yes

Google SheetsAdd a row to destinationTabName in destinationSpreadsheetLink.Add a row to destinationTabName in destinationSpreadsheetLink.

No

No steps

No

No steps

Set copiedCount to 0.Set copiedCount to 0.

Set blankKeyCount to 0.Set blankKeyCount to 0.

Set failedCount to 0.Set failedCount to 0.

Set rowNumber to 0.Set rowNumber to 0.

For each sourceKey in sourceKeys:For each sourceKey in sourceKeys:

Add 1 to rowNumber.Add 1 to rowNumber.

If rowNumber is 1If rowNumber is 1

Yes

Skip to the next item.Skip to the next item.

No

No steps

If copiedCount is greater than or equal to maxNewRowsPerRunIf copiedCount is greater than or equal to maxNewRowsPerRun

Yes

Stop repeating.Stop repeating.

No

No steps

If a step below fails, save the error as rowErrorIf a step below fails, save the error as rowError

Try

Trim sourceKey and save it as rowKey.Trim sourceKey and save it as rowKey.

If rowKey is emptyIf rowKey is empty

Yes

Add 1 to blankKeyCount.Add 1 to blankKeyCount.

Else if @copiedKeys doesn't contain @rowKey

Google SheetsRead row rowNumber of sourceTabName in sourceSpreadsheetLink. Save it as sourceRow.Read row rowNumber of sourceTabName in sourceSpreadsheetLink. Save it as sourceRow.

Set rowValues to sourceRow.values.Set rowValues to sourceRow.values.

If rowValues isn't emptyIf rowValues isn't empty

Yes

Google SheetsAdd a row to destinationTabName in destinationSpreadsheetLink.Add a row to destinationTabName in destinationSpreadsheetLink.

Add rowKey to the end of copiedKeys and save it as copiedKeys.Add rowKey to the end of copiedKeys and save it as copiedKeys.

Add 1 to copiedCount.Add 1 to copiedCount.

No

No steps

Otherwise

No steps

On error

Add 1 to failedCount.Add 1 to failedCount.

Show in the output: Could not copy row rowNumber of the source tab: rowError.message.Show in the output: Could not copy row rowNumber of the source tab: rowError.message.

Set runSummary to Copied copiedCount new row(s) into '@destinationTabName'.Set runSummary to Copied copiedCount new row(s) into '@destinationTabName'.

If blankKeyCount is greater than 0If blankKeyCount is greater than 0

Yes

Change runSummary to runSummary blankKeyCount row(s) had nothing in the key column and were left alone.Change runSummary to runSummary blankKeyCount row(s) had nothing in the key column and were left alone.

No

No steps

If failedCount is greater than 0If failedCount is greater than 0

Yes

Change runSummary to runSummary failedCount row(s) could not be copied and will be tried again next run.Change runSummary to runSummary failedCount row(s) could not be copied and will be tried again next run.

No

No steps

If copiedCount is greater than or equal to maxNewRowsPerRunIf copiedCount is greater than or equal to maxNewRowsPerRun

Yes

Change runSummary to runSummary This run stopped at its limit of maxNewRowsPerRun rows; the next run carries on from there.Change runSummary to runSummary This run stopped at its limit of maxNewRowsPerRun rows; the next run carries on from there.

No

No steps

Show in the output: runSummary.Show in the output: runSummary.