Uploading Spreadsheet (Excel, Google Sheets) data into Looker

excel
googlesheets
spreadsheet
upoload

#1

Although Looker does not have functionality to directly connect to an Excel spreadsheet, there is a way to upload data via a derived table.

This method works if you have limited number of columns and rows, for example monthly forecasting data, but is not very scalable for large spreadsheets. It is also static and will not automatically update if the underlying spreadsheet data changes.

If this example, we have some simple forecast data by Month and Region.

Using the “concatenate” function we can create SQL Select statements to generate a table. The first line would be a simple Select clause and the subsequent lines would Union each row as a new Select clause.

Line 1 would look like:

=concatenate("select ","'",A2,"' as month, '",B2,"' as segment, '",C2,"' as forecasted_value")

Then each subsequent line would look like:

=concatenate("union select ","'",A3,"', '",B3,"', '",C3,"'")

The resulting SQL statement then becomes:

We can then paste this SQL into our SQL Runner to generate a table:

Then, using Looker’s “Add to Project” option we can create a View file in our Project.

Looker will create Dimensions from each column in the table. This new View file can then be joined into an Explore as needed.

Note that there are some caveats around this process:

  • the new derived table is static, any changes to the underlying spreadsheet will not automatically be reflected in the LookML View file. however, if the underlying data changes but the column names stay the same, you can easily cut-n-paste the updated SQL directly from the spreadsheet in to the LookML View file and skip the intermediary steps.

  • this process is great for smaller datasets but for spreadsheets with a large amount of columns or rows or data that will change in structure, may not be scaleable.

  • all columns will come across into the View file as type: string. any dates or numbers may need to be converted into their true data types.

  • don’t forget to assign a primary key.


(Pruthviraj Shivanna) #4

Dear Looker Team,
Is this still the case, can we not import google sheet as an external source and still see the updated content ?
How about using google sheet as a connection and importing the sheets as view ? is that available yet ?

I can always make a bigquery table to refer to google sheet and then import that as a view. But I just wanted to remove the middle man and make it easy for the business users to automatically import a sheet.

Thanks for your help.
Regards


(milli.koch) #5

Hi @Pruthviraj_Shivanna,

There isn’t currently a way to connect Looker directly to Google Sheets. The way to import data from Google Sheets into Looker at this time would be to either use the method described above for limited datasets or to upload the data to BigQuery first, like you mentioned.

Those are good suggestions though – I’ll make sure to pass your feedback on using external tables and connecting directly to Google Sheets along to our product team!

Best,
Milli