Integrating Excel data in Glide through Google Sheet/Drive

Ask for Help
1

Hello - I’m looking to automate integration of data from Excel into Google Sheets.
I’m extracting data from a business tool to Excel quite easily on a specific rythm (ie: twice a day), I can sync up these data in Google drive, by synchronizing the folder where I store locally the Excel files.

But to be able to get this data into Glide app, I need to pull them into a google sheet file. I’m struggling to do this automatically.
I have been testing google sheet add on “Sheet-to-go” on trial, that seems to do the job, but to integrate .xls file, I’ll need to buy the advanced version, which is 230$/year, that is a bit too much for my project.
Also not sure that I’ll be able to have fancy features (like using arrayformula), when integrating excel data to google sheet with this add on.

Has anyone already face this use case? Tried “Sheet-to-go”?
Any advice are welcomed!
Thank you for the support!

1 Like
2

Have you looked into Zapier? Not sure it does exactly what you need but worth checking out.

3

Have you looked into the business tool that you are using to extract the data to see if there is any other option to extract it to a CSV file. You could regularly import this into a Google sheet very easily by-passing Excel.

4

Thank you, I’ll give a try going through csv. Any hint on how to easily integrate .csv to google sheet automatically/regularly?

5

Here is the top hit when I searched “google script import csv” on Google:

6

If your CSV is on a server, you can copy the link and use the formula = IMPORTDATA ()

7

Thanks. Indeed IMPORTDATA() is the right formula to work with. It is also possible to use it if the CSV file in GDrive, by getting the ID of the file, but the CSV file has then to be public…
I’m also struggling to get the result of the IMPORTDATA() formula to be updated when the CSV file is updated.
Do you know how to force the update of the formula result? For now I have to remove the formula from the cell and put it again…

8

Try the following:
Somewhere in your file include a now() function. Than, go to the file setting and change the calculation to every minute and change. This could help make the file update as the now funaction will trigger it to run

9

@Julien the link I shared above has a simple script function that does exactly what you want if you have the CSV file. By using a timed trigger you could have it run at pretty much any timed interval you would want and the data would always be the latest. Yes importdata() sheet function kind of works, but as you have seen you need to modify it to get it to refresh. I understand a hesitation to venture into a script but this one seems to be a no brainer for me, and a great place to see the power of using them.

10

The NOW() function doesn’t force the IMPORTDATA() function to update.
I’ll try with the script, and @George_B you are right it is the good place to try and explore the power of the script. I’m still facing issue to get the script works, but I’ll learn from this.

11

What is your issue with getting it to work?

12

Getting in error in the script with this line, extracted from the link you pointed: var sheet = SpreadsheetApp.getActiveSheet();
it sends back null for sheet. Don’t know how to point to the gsheet file and specific sheet where I want the .csv data to be imported/printed.

13

Ok forget my comment, I was not linking the script to the sheet… I really need to spend time on this script feature!
So with the script, I can do what I wanted to, and enable data automation from my Business App and glide in one way. I need know to spend time to see how I can do in the other way.
Thank you for the support and guidance.