Get started by naming your job and selecting your Load Target -> Google Sheets.
(Currently Google Sheets and BigQuery are supported... learn how to set up a BigQuery sync job)
A job in Local Hero entails uploading data from a source CSV file into a Google Sheet. In order for the app to function properly, a target sheet will need to have a target data definition by having including columns in the first row of the sheet.
- In order to create a job with Local Hero you must first select the target Google Spreadsheet. Copy and paste the ID or full spreadsheet link into the Google Spreadsheet ID. It is recommended the target sheet has a title that illustrates its purpose.
- The target tab in the Spreadsheet must contain headers in row 1. It can contain any headers you like, but using the same headers as exist in the source CSV file will make mapping much easer.
- Once the Spreadsheet URL or ID has been pasted into step 2, click Get Sheets & Headers. This will upload the spreadsheet data into Local Hero. Then choose the target tab in the Spreadsheet that contains the headers you want Local Hero to use.
After selecting the target Google Spreadsheet, click Choose File to select the source CSV file and locate within your local filesystem. Once the file is loaded, you can explore its contents by clicking on the colored tile that represents it. You can also join secondary files and key them together into a single data source provided there is a uniquely-keyed (match-lookup) relationship between them.
Once all of those steps are completed, click NEXT.
Note: In order for scheduled jobs to run without failure, it is important that the folder location and filename of the CSV remain constant within your SIS export file delivery process. In other words, when setting up your scheduled export job, your new export file will need to assume an identical name and directory location, overwriting the old file.
Next, Local Hero wants to map CSV data to its destination. Indicate which columns to include, and how you want columns to be mapped, from the source CSV to the destination Sheet. Clicking Automap Source to Target will attempt to match columns based on the row 1 headers in the CSV and the selected destination Spreadsheet tab.
You can also custom map and concatenate fields and string values via the expression builder, which can be accessed via the gear icon next to each destination field.
Choose how you want Local Hero to handle updates. Select if and how you want source data to update records in the destination Sheet. There are four actionable options:
- Clear and replace data in destination sheet: All data below the header row in the destination sheet will be cleared and replaced each time the app runs.
- Append source records in destination sheet: New data will be appended below the last data row in the destination sheet each time the app runs.
- Update records based on unique key match: Each time the app runs, mapped values in the destination sheet will be updated with new information from the source CSV file based on matching record identifiers. Records will only be updated if they have a match in the source CSV and no new records will be added.
- From a dropdown menu, you are able to select the Source Key and Destination Key to define the matching relationship.
- Upsert records based on unique key match: Each time the app runs, mapped values in the destination sheet will be updated with new information from the source CSV file based on matching record identifiers. If new records are found in the source CSV, they will be added to the destination sheet.
Next, set your automation preferences:
When you're done, you should see your job listed in the Your Sync Jobs list, where you can monitor last run time, run manually, link to the local CSV and Sheet, and disable / pause it.