Hey everyone! I’m struggling with a tricky situation. I need to automatically sync data from an Excel file stored in my work Google Drive to a Google Sheet. The end goal is to display this data in Looker Studio.
I’ve tried a few things:
- IMPORTRANGE doesn’t work because the source is an xlsx file
- IMPORTXML gives me a ‘URL not found’ error
The files are in a business Drive account. I’ve set the permissions, but it’s still not working. Oddly enough, it works fine with my personal account, but not with the work one.
Has anyone faced this before? Any ideas on how to make this work? I’d really appreciate some help figuring this out. Thanks in advance!
hey there! have u tried using google apps script? it can convert excel to sheets automatically. u could set up a script to do this regularly, then import the data to ur main sheet. might need to tweak permissions in ur work account tho. good luck!
Have you considered using Google Apps Script with time-based triggers? It’s a powerful solution for your scenario. You can write a script that converts the Excel file to Google Sheets format, then copies the data to your target sheet. Set up a trigger to run this script at regular intervals, like hourly or daily.
Here’s a quick outline:
- Write a script to convert Excel to Sheets
- Copy data to your target sheet
- Set up a time-based trigger
This method bypasses permission issues since it runs under your account. It’s also more reliable than third-party tools. The initial setup takes some coding, but it’s free and highly customizable. Let me know if you need help with the script!
I’ve dealt with similar sync issues before, and one solution that worked for me was using the Google Drive API directly. It’s a bit more technical, but it gives you full control over the process.
Here’s what you could do:
- Set up a Google Cloud project and enable the Drive API.
- Use a programming language like Python to write a script that:
- Authenticates with your work Google account
- Finds the Excel file in your Drive
- Downloads it locally
- Converts it to CSV (there are libraries for this)
- Uploads the CSV to Google Sheets using the Sheets API
You can then schedule this script to run periodically (e.g., daily) using a task scheduler or cloud function.
It takes some initial setup, but once it’s running, it’s pretty hands-off and reliable. Plus, it bypasses any permission issues you might be having with other methods.
Let me know if you want more details on any part of this process!