Get/Sync Data from Looker to Excel

[Klairi]

I have an Excel file which I need to populate with data from Looker. The excel file is sitting in the SharePoint. I was wondering if there is a way to pull the data from Looker to the Excel file in SharePoint without downloading every table and copy paste them. 

Can we do something like this in Looker? Do I have to sync them?

If this is impossible to happen when file is at SharePoint, can I approach it in a different way?

Any advice will be helpful.

Thank you

[Dawid]

you need to have your data in relational database / data warehouse

[IanT]

Read about “public looks”.

[Roderick]

Hi @Klairi, it's possible, but may require some APIs based on your set up.

Yes, it is possible to pull data from Looker to an Excel file in SharePoint without downloading every table and copy pasting them. There are a few ways to do this:

1. Use the Looker API. The Looker API allows you to programmatically access data from Looker. You can use this API to create a script that will pull the data from Looker and write it to an Excel file in SharePoint.
2. Use the Looker Data Connector for Excel. The Looker Data Connector for Excel is a third-party tool that allows you to connect Looker to Excel. Once you have connected Looker to Excel, you can use the Looker Data Connector to pull data from Looker and write it to an Excel file.
3. Use Looker's built-in export functionality. Looker has a built-in export functionality that allows you to export data from Looker to a variety of formats, including Excel.

Here are some additional details about each of these methods:

• Using the Looker API. The Looker API is a powerful tool that allows you to programmatically access data from Looker. You can use this API to create a script that will pull the data from Looker and write it to an Excel file in SharePoint. To learn more about the Looker API, you can visit the Looker documentation.
• Using the Looker Data Connector for Excel. The Looker Data Connector for Excel is a third-party tool that allows you to connect Looker to Excel. Once you have connected Looker to Excel, you can use the Looker Data Connector to pull data from Looker and write it to an Excel file. To learn more about the Looker Data Connector for Excel, you can visit the Looker Data Connector for Excel website.
• Using Looker's built-in export functionality. Looker has a built-in export functionality that allows you to export data from Looker to a variety of formats, including Excel. To export data from Looker, you can follow these steps:

1. Go to the Looker dashboard that contains the data you want to export.
2. Click the Export button in the top right corner of the dashboard.
3. Select Excel from the list of export formats.
4. Click the Export button.

Looker will then export the data to an Excel file. You can then save this file to SharePoint.

• Using Microsoft Power BI. Microsoft Power BI is a business intelligence tool that can be used to connect to Looker and create reports and dashboards. To use Power BI to connect to Looker, you can follow these steps:

1. Open Power BI.
2. Click the Get Data button.
3. Select Looker from the list of data sources.
4. Enter your Looker credentials.
5. Click the Connect button.

Once you have connected Power BI to Looker, you can create reports and dashboards that can be viewed in Excel. To do this, you can follow these steps:

1. Drag and drop the tables and measures from the Looker data model into the Power BI canvas.
2. Format the data as needed.
3. Click the Publish button.
4. Select Excel from the list of publishing options.
5. Click the Publish button.

Power BI will then publish the report or dashboard to Excel. You can then open this file in Excel and view the report or dashboard.

Hope these suggestions help! 

[tushar_1992]

Hi Roderick,

I too have a similar requirement. I am planning to use Looker API for the same. Is it possible for you to please tell me the name of API to be used? I tried to search in API Explorer 4.0 but could not figure out exactly which API to use as there are 429 methods.

Thanks,

Tushar 

[DougVanHorn]

@Roderick ,  I am standing up a new data analytics platform using GCP/Big Query and Looker to provide self-service dashboards to internal stakeholders.   Our legacy platform provides formatted Excel files that are emailed to the stakeholders on a daily, weekly, monthly, quarterly, and year end time periods.   I see from above that the Looker API allows for the creation of Excel files as the current functionality of Looker doesn't provide the download of an entire dashboard in Excel.   It does provide the ability to download individual Looks into Excel.

I was wondering if you had an example of how to use the Looker API to generate an Excel file from Looker data?   I am under a tight timeline to get this into production and any jump start would be greatly appreciated.

Thank you in advance!

Doug Van Horn

[a_shah]

Hi @Klairi - I know a way that should work with excel files on your desktop. It may work on sharepoint aswell but I do not have access to one to test.

1. Go to the Look and edit setting

2. Enable public access and save it, then again come back to the same screen and copy the last option i.e. Excel Web Query and download the .iqy file.

3. This iqy file can be used in excel to retrieve data from Look into the excel. For how to use iqy file in excel, please check this link.

~Ashish

[guneev09]

Hi @a_shah ,

I tried above setting on the look but it only downloads 500 rows, is that the limitation with iqy as well

[a_shah]

Hello @guneev09  - I have not tested this but I think the default row limit setting in Explore is the cause. Can you try updating the row limit of Look, save it and then generate the iqy file for it and then try?

~Ashish

[guneev09]

API calls based on an Explore, a Look, or a dashboard are subject to the 5,000 row limit, this might not work for my use-case. We want to download the entire data what the look renders to the excel file