The Unique Identifier on a Google Sheet is important for the read/write process with the Google Sheet Synapse. The Google Sheets Synapse identifies each row in a sheet as a record in the Google Sheets Entity. The unique identifier for a record in the Google Sheet Entity is set on the Source and Destination sheets in different ways. This article will help you understand the differences between how Syncari reads/writes a unique identifier for each record in a Google Sheet source or destination file.
Source Sheet Configuration
There are 3 methods for defining the unique identifier on a Google Sheet that is being used as a Source in your pipelines.
1. Define the SyncariId column in the Source Sheet
If your source sheet will be updated in a way that will cause new records to be inserted in the middle of the sheet or updated so that records change row positions, you will need to define your unique identifier. Define the Unique Identifier by setting the 1st column on the sheet with the header nameSyncariId
The value for each row under the SyncariId column must be populated with a unique value. This can be something like an email address (assuming you will not have duplicates in the sheet) or a UUID for each record.
2. Configure the ID field on the Google Sheets Entity Schema
You can use an existing column to define the unique identifier if your sheet is populated with a column that represents a unique identifier for each record. Follow these steps to define the column on the source sheet as the unique identifier.
- Go to Schema Studio and select your Google Sheets Synapse
- Select the Entity related to the Google Sheet that you will use as a data source. Click the
menu icon, select New Draft, then click on the Fields button to edit the fields on the entity.
- Select the field that you would like to use as the Source Sheet's Unique Identifier and Click Edit Field on the right panel.
- Select the "ID Field" checkbox. Then click Save.
- Finally, click the Publish button at the top of the screen to save your change
3. Default (We do not recommend this option)
By default, Syncari will determine the unique identifier for each row (record) on a source sheet as the Google Sheet's unique system ID along with the row number of the record. For example,
1k6eDYMmWXARVHmov-2CA_BsrUo7G1MeHFEKFe3WOp58-sheet1-49
Destination Sheet Configuration
In order to update existing records in a Google Sheet that's set as a destination node in your pipeline, Syncari requires a column (field) on your Google Sheet to include a unique identifier for each record. After activating your Google Sheets Synapse, add the SyncariId header into your destination sheet along with the other header names.
The SyncariId column will be populated with the SyncariId value which is a unique identifier for each record. This gives Syncari the ability to update existing rows in your sheet from the related Syncari record.
Fantastic! Now you are ready to sync updates to existing records in your Google Sheet as a destination node in your pipeline.