Create a Google Sheets sync job

Creating a sync job to Google Sheets

To create a sync job, do the following:

  1. Open Local Hero.
  2. Click + Create your first sync job.
  3. Enter the Job Name in the field provided.
  4. From the Load Target dropdown, select Google Sheets
    SheetsSyncJobNameHL.png
  5. Click Next to advance to Source data & Target.

Selecting source data

Next, you’ll select the source CSV file that contains your SIS source data and the Google Sheet you want to contain the data. The Google Sheet is your target. 

To select a data source, do the following:

  1. From Source data & Target, click CHOOSE FILE.
  2. Navigate to the location where the file is stored. 
  3. Select the file.
  4. Click Select source CSV file. The file is listed in the Source Data section. 
  5. 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.
    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.
  6. Load additional Source Data if necessary. 
  7. From Load Target, enter the Google Spreadsheet ID or URL.
    Note: Your target Sheet should have a title that illustrates its purpose and the target tab must have column headers in the first row of the sheet. It can contain any headers you like, but using the same headers as exist in the source CSV file will make mapping much easier.
  8. Click Get Sheets & Headers. This loads the spreadsheet data into Local Hero.
  9. From the Target Sheet within field, select the sheet from the dropdown.
    SheetsSyncJobSourceTargetHL.png
  10. Click Next to advance to Target mappings.

Creating target mappings

Next, Local Hero wants to map CSV data to its destination. You can manually map columns or Automap to match columns.

Manually map columns

You can indicate which columns to include, and how you want columns to be mapped, from the source CSV to the destination Sheet.  

  1. From Target Mappings click the gear icon next to a field to open the expression builder to map the field to the correct source data field in your CSV file.
    SheetsSyncJobTargetMapStartHL.png
  2. Within the mapping dialog for a given field, you can concatenate fields, insert text, and utilize a small set of date, string, and anonymization functions.
    SheetsSyncJobExpressionHL.png
  3. Click Save
  4. Continue until all fields are mapped. 
  5. Click Next to advance to Choose Action.

Automap source columns

You can 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.

  1. From Target Mappings click the Automap Source to Target.
  2. At the Are you sure prompt, click Confirm.
    SheetsSyncJobAutomationHL.png
  3. Click Next to advance to Choose Action.

Choosing an action

In this step, you’ll choose source data to update records in the destination Sheet. You’ll choose one of the four actions.

  1. From Choose Action select an option.
    Note: For record-level update functionality, see Sheets sync jobs.
    SheetsSyncJobActionHL.png

    Setting

    Description

    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.

  2. Click Next to advance to Automation settings.

Working with automation settings

This step lets you specify how the job is run. Options include manually, automatically at a specific time of day, or every hour. You can also opt to send an email notification to a designated list of email addresses.

  1. Select automation settings for this job.
    SheetsSyncJobAutomationHL.png
  2. (Optional) In the Admin Notifications filed, enter an email address to send sync notifications. Setting admin notifications email address(es) will send an email that looks like this each time the job runs.
    Screen_Shot_2017-07-26_at_2.59.23_PM.png
  3. Click Save.
  4. At the saved successfully prompt, click View Job List to view jobs.
    SheetsSyncJobListHL.png