How do i get data from google sheets?

• Member
• 75 replies

You can export data directly to Google Sheets using the Google Sheets action from the Looker Action Hub rather than implementing the method described in this article. See the Looker Actions — Google Sheets Help Center article for more information.

Looker supports exporting a public Look to Google Sheets. In this post, we present some functions that make the process smoother.

Basic setup

Looker admins must enable the Public URLs setting.

To obtain the function that enables you to import data from a Look into a Google Sheet:

1. Navigate to the Look that you want to export to a Google Sheet. Click the gear menu and select Edit Settings.
2. Copy the function under Google Spreadsheet. This function includes the ImportXML function that is built into Google Sheets.
3. Paste the function into the upper-left cell of the Google spreadsheet where you would like your data to appear, and then press Enter.

Refreshing your data

The Google spreadsheet ImportXML function uses a caching mechanism that can make it difficult to predict when data will be refreshed in the Sheet.

Manual update: Google's import function

You can force a data update by adding certain URL parameters to the URL part of the ImportXML function. For example, appending cachebust=123 to the URL creates a unique URL — without changing the settings in the original Look — that effectively forces a request to the Looker server to retrieve fresh data, rather than retrieving from the browser cache. If there are no URL parameters yet, you'll need to add a question mark to the end of the URL first, like this:

  =ImportXML("https://demo.looker.com/looks/ab1cd2.html?cachebust=123", "//tr")

Other parameters can also be added, but these may affect the Look settings. Separate any additional parameters with an ampersand (&). For example:

=ImportXML("https://demo.looker.com/looks/ab1cd2.htmlapply_formatting=true&cachebust=123", "//tr")

Automatic update: custom import function

The Google spreadsheet ImportXML function uses a caching mechanism that can make it difficult to predict when data will be refreshed in the Sheet. See the Looker Google Sheets Import Community post for a workflow that can be used in lieu of ImportXML to refresh the data more reliably. Please note that this method is not officially supported by Looker.

Use the Create Google Sheet row action or Update data in a Google Sheet action to send data from HubSpot properties to your spreadsheet. For example, you can use this action to send contact data from new leads to a spreadsheet for your sales outreach team.

Before you can add data to Google Sheets with workflows, you'll need to connect Google Sheets to your HubSpot account. You can connect the app from the App Marketplace or from within a workflow.

Before you get started

Keep the following in mind when sending data to Google Sheets from HubSpot:

• When setting up your spreadsheet, do take note of the following:
  • The header row must start in the first column of the first row.
  • Each column header should be labeled so that HubSpot can identify and send data to it. HubSpot cannot send data to unlabeled columns.
  • Ensure all columns in your table range have column headers. Avoid having blank column headers between columns as this can cause issues and prevent the spreadsheet from updating as expected. For example, if your spreadsheet uses columns A to C, do not leave the column B header blank.     
  • Only the first 100 columns in a spreadsheet can be updated. If a column after the first 100 columns is selected when setting up the workflow action, it will not be updated.
  • The spreadsheet cannot exceed 5,000,000 cells. 
• By default, HubSpot sends internal property values to Google Sheets. For dates, HubSpot sends unix timestamps in milliseconds.
  
  • If you have an Operations HubProfessional subscription, you can format the date property into your desired format by adding a Format data action before the Create Google Sheets row action. Then, in the Property dropdown menu of the Google Sheets action, select the date output from the Format data action. 
  • You can also convert dates from milliseconds in Google Sheets by adding the following formula to a separate column in the spreadsheet: =A1/1000/60/60/24 + DATE(1970,1,1). Replace A1 with the column that the unix timestamp is in.

• When using the Create Google Sheet row action, data is not added to existing rows. Instead, the action inserts a new row into the sheet. This can affect referenced cells and any formulas used. To avoid this, ensure that only populated, existing rows are referenced.   

• Avoid manually adding data to the sheet that HubSpot is sending data to. If you need to manually add data to the spreadsheet, you should create a new sheet and reference existing data from the first sheet.

• When selecting a spreadsheet in the workflow editor, the sheets available are tied to the sheets that connecting user has access to. For a sheet to appear as an option, it must meet at least one of the following requirements:
  • Belong to the connecting user's drive.
  • Previously shared with the connecting user.
  • Belong to a shared drive that the connecting user has access to.
    
• If you disconnect Google sheets and reconnect with another user, the sheets available may be different. It's recommended that you check any dependent workflows before disconnecting and reconnecting. 

Set up your workflow

Before you can add data to Google Sheets with workflows, you'll need to connect Google Sheets to your HubSpot account. You can connect the app from the App Marketplace or from within a workflow.

To add data to Google Sheets in a workflow:

• In your HubSpot account, navigate to Automation > Workflows.
• To edit an existing workflow, hover over the workflow, then click Edit. Or, learn how to create a new workflow.
• In the workflow editor, click the + plus icon to add an action. 
  

Add new Google Sheet rows

When using the Create Google Sheet row action to update your Google sheet, ensure your spreadsheet has been correctly formatted. Aside from the header row, do not include any other data in the sheet you're sending data to.

To update your existing Google Sheet rows:

• In the actions panel, scroll down and select Create Google Sheet row.
  

• In the Spreadsheet dropdown menu, select the spreadsheet you want to send data to. You can select from the spreadsheets that are owned by or have been shared with the user who connected Google Sheets to HubSpot. 
• In the Sheet dropdown menu, select the sheet you want to send data to. The Sheet field refers to the tabs at the bottom of your spreadsheet.
• In the Header and Property dropdown menus, select the columns to send data to and which properties you'll send data from. HubSpot will add data to empty rows below any existing data in the sheet. Each action can add up to 20 cells of data per row.
• Click Save.

Update existing Google Sheet rows

To update your existing Google Sheet rows:

• In the actions panel, scroll down and select Update data in a Google Sheet.

• In the Spreadsheet dropdown menu, select the spreadsheet you want to send data to. You can select from the spreadsheets that are owned by or have been shared with the user who connected Google Sheets to HubSpot. 
• In the Sheet name dropdown menu, select the sheet you want to send data to. The Sheet name field refers to the tabs at the bottom of your spreadsheet.
• Set up identifiers to determine which rows to update: 
  • In the Lookup column header dropdown menu, select the header of a column in your spreadsheet, corresponding to a HubSpot property to use as an identifier for the update.  
  • In the Lookup column property dropdown menu, select a property corresponding to the Lookup column header to use as an identifier for the update. When the value of a cell in the selected Lookup column header matches the value of the selected HubSpot property for the enrolled record, the row containing the matching cell will be updated. If there are multiple rows with matching values, only the first row containing the matching cell will be updated.
• In the create a new row if none match dropdown menu:
  • If you want to create a new row if there is no matching Lookup column header and Lookup column property, click Yes. This will create a new row containing only properties set in the Select columns to update section.   

• • If you do not want to create a new row if there is no matching Lookup column header and Lookup column property, click No. 
• Set up the columns in the spreadsheet that you want to update: 
  
  • Click the Select columns to update dropdown menu, a list of column headers from the selected spreadsheet will display. Select the column that you want to update.   
  • Click the dropdown menu under the column header and select an option. You can either choose to update the data with an existing property value from the enrolled record or with the outcome of an earlier workflow action.
  • To add another column to update, click the Select columns to update dropdown menu and select another column. You can add up to 100 columns to update your data. 
  • To delete a column, click thedeletetrash can icon next to the column header.     

• Click Save.

How do I extract data from Google Sheets?

Can you pull data from Google Sheets to excel?

How do I access data in Google Sheets?