Using the Code Importer - Import Data from Google Sheets

Snapshot

This is a tutorial about importing data from a CSV file hosted on cloud storage or a Google Sheet into Stae. We'll walk you through this process by using the Detroit Murals Inventory as a Data Source. To see more information about this data set, visit the Detroit Mural Project and view the murals map on Detroit's public portal.

At the end of this tutorial, you’ll be able to

• Create a new Data
• Import a data set from Google Sheets
• Use Stae's Importer to transform data using JavaScript

One of Detroit's many murals documented by Viranel Clerard of the Detroit Murals Project.

1. Create a Data Source

To create your own Data Source, navigate to "Create Source" on the Sources Page.

• Step 1: Name your source and give it a simple description.
• Step 2: Choose whether you’d like the Source to be public or private.
• Step 3: Search for the Data Type that best matches your Data Source. (For the Detroit Mural Data set, Artwork is the appropriate data type).
• Step 4: Select the "Fetch" option to pull data from Google Sheets.
  
  

Select "Create a Source" from the Sources page

Step 1: Add a name and description for your Data Source

Step 2: Publish the source to the city and select your desired visibility. Stae Admins can select different visibility permissions based on their preferences.

Step 3: Select the Data Type that maps to your Data Source. In this example, the mural data will be represented with the Artwork Data Type.

Step 4: Select "Fetch" and then click save to create your new Source.

2. Publish your Google Sheet Data

After creating your Data Source, open the Google Sheet you'd like to import from your Google Drive. Select File > Publish to Web > Select your sheet name and then select CSV on the dropdown options. This will be a live link to your spreadsheet so that data updates at the interval your Importer runs. Copy the URL and then navigate back to Stae.

To export a CSV from your Google Sheet, select File > Publish to Web > select your sheet name and then select CSV under the dropdown options.

3. Create an Importer for your new Data Source

Now that you've created a new Data Source, you're going to create an Importer that will fetch data from an API. If you're working from a different city, the process will be the same as importing data from the city's open data portal.

• To begin, select Create Importer on the Data Source's details page.
• Step 1: Copy the URL from your Google Sheet to the "address" field and test the link to see a preview of the data set you'll be importing. Make sure File and CSV are selected.
• Step 2: Select the code tab and copy the Importer code from the code sample provided and paste it in. Select Test Now to see the result. If you're using the Murals Data, you should see the mapped output on the right side. See the Importer Utilities tutorial for more troubleshooting and documentation.
• Step 3: Select a time interval at which you'd like the Importer to run and collect data
  from Google Sheets.
  
  

On the Source Details page for the Data Source you just created, navigate to the Importers section and select "Create Importer".

Step 1 - Copy the URL for your data

Paste the Mural Data CSV address from Google Sheets.

Step 2 - Copy and paste the importer code 

Below is the code you'll be using to import the CSV from Google Sheets, along with an explanation of each section in the comments. Visit the importers GitHub repo to copy this code over.

Paste the Importer's code from the GitHub repo above to Step 2 and select "Test Now" to get a preview of the output.

Step 3 - Name and describe your importer

Name and describe your importer. Next, you'll assign a regular interval to import your data to Stae. Select "save" when you're ready to import the data.

4. Monitor the Importer's Progress

You can monitor the Importer's progress on the "Jobs" page. Select this link on the Importer section. Select the dropdown on the Importer currently running to see its current progress. There is also a running count that details information about the integrity of the data being transformed.

View of "Jobs" in the Mural Importer.

Job Status Definitions

The job status will give you an idea on the health and integrity of your datasets. When there are errors, the log will return a response on what caused the problem.

Success: The row was successfully imported

Skipped: Matched your filter and ignored this row

Errored: The transform function had an error and the row was not imported. This could be due to data containing bad rows, for example invalid location coordinates. Check the job status log for the importer to trace the error.

Rejected: The row was not imported because it didn't meet a required validation such as having a unique ID or a location. For example, in the Building Permits dataset, some permits were missing locations so those rows would be rejected. Check out the data type's validations as well as the job status log to trace the error.

Stae Inspired

*“The essential characteristic of the city ... is that it demands participation.”*
Lawrence Halprin, Cities

Have any questions or running into issues with this feature?
Reach us at: support@stae.co