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.