The first line would be a simple SELECT clause, and the subsequent lines would UNION each row as a new SELECT clause. Using the CONCATENATE function, we can create SQL SELECT statements to generate a table. The following example highlights how to implement the second method described above.įor example, we have some simple forecast data by month and region: This method is also static and will not automatically update if the underlying spreadsheet data changes. This method works only if you have a limited number of columns and rows, such as monthly forecasting data, and is not very scalable for large spreadsheets. The secondary method utilizes the CONCATENATE function to create a series of SELECT statements to ultimately create a SQL based derived table. However, if you have security concerns, there is second, more secure, method. The script should be added to your desired output Google Sheets.ĭo not forget to add the Drive API service to your script.Looker will not be updating this content, nor guarantees that everything is up-to-date.Īlthough Looker does not connect directly to an Excel spreadsheet, there are ways to upload data via a derived table.įirst, there is a third-party CSV to SQL Tool with which you can easily convert a CSV file into a SQL UNION ALL query, and generate a LookML derived table file to copy and paste into the appropriate project. You may modify the script to be suitable for your current issue. Importing the data from the temporary Google Sheets file to the desired/output Google Sheets file.ĭeleting the temporary Google Sheets file.Įxpect a longer runtime when applying this script to a bigger excel file. Return (spreadsheet.id) //Returns the ID of the converted fileĬonverting the Excel file to a temporary Google Sheets file.
You may use the following script as a basis for yours: function importData() ],
As a workaround, you need to first convert your excel file to Google Sheets and then extract the data from the converted file to your output Google Sheets file. Unfortunately, there is no direct way to extract data from Excel files to Google Sheets using Google Apps Script. Suggestion: Temporarily Convert the Excel File to Google Sheets File to Extract Data
0 kommentar(er)
