Checks a source sheet hourly and appends any rows it has not copied yet into a second sheet. One-way only - nothing is overwritten.
Uses
Google Sheets
Copy new rows to a second spreadsheet
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 destinationTabNameYes
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 emptyYes
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 emptyYes
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 emptyYes
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 1Yes
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 maxNewRowsPerRunYes
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 rowErrorTry
Trim sourceKey and save it as rowKey.Trim sourceKey and save it as rowKey.If rowKey is emptyIf rowKey is emptyYes
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 emptyYes
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 0Yes
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 0Yes
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 maxNewRowsPerRunYes
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.