Hi @Cora, what do you mean by “open an excel”?

Using the Schedule by Zapier trigger you can trigger the Zap every Monday at 8am. What would your action be?

Hi @MarijnVerdult 

Thank you for your response. 

The action would be to open a specific Excel file.

That needs to be done in order for the file (or rather a couple of files) to update/refresh its data. It would make it easier for me, instead of setting a reminder and navigating to the files, for them to just pop open, do the refresh and all I have to do is to close them again.

This way the data will flow nicely into datasets in Power BI.

Greets

Cora

Ah I get it, you want to recalculate all the fields and formulas in the Google Sheet, right?

There might be an easier solution than Zapier for that, check this thread of solving this using AppsScript:

https://stackoverflow.com/questions/31659186/how-to-refresh-a-sheets-cell-value-in-google-apps-script

1. In the spreadsheet, go to Tools > script editor

2. Insert the code to get the value of a random cell, put that value back, and flush the spreadsheet

3. Click the Save icon and name the project

4. Go to Edit > Current project's triggers

5. Click +Add Trigger at the bottom-right corner

6. Choose the function to run: myFunction. Select event source: Time-driven

7. Click Save

Depending on your formulas you might also check out the standard auto-recalculation: https://spreadsheetpoint.com/auto-refresh-google-sheets/

Hi @MarijnVerdult 

Well, it already does the refresh (it has an integration (gets data from a web source) to an App called CommCare) but the file still needs to be opened for the refresh to take place. Well, sometimes you have to verify the login again but usually, all you need to do is open the Excel file (not a Google Sheet), wait a few seconds, save and close it again.

So this is all I need the Zap to do. Open that Excel file.

Greets

Cora

Ah, thanks for the explanation. How familiar are you with Python? One way you could try is opening the file using the Zapier Code Step.

You could for example try

request.get

but I’m not sure if this will trigger the refresh

Thanks @MarjinVerdult

Hmm, Python seems to be a handy skill nowadays. Unfortunately all I know is that you can use it for quite a lot of helpful programming ;)

But I might research a little about that request.get snippet and see where I can go with this.

Thanks for all your support. It’s highly appreciated.

@Cora - this might help you get started:

r = requests.get("https://drive.google.com/")
status = 'Success!' if r else 'An error has occurred.'

output =  {'response_url': r.url, 'response_status': status}]

you would need to change the URL on the first line of course and please check if Excel counts this as an open...

Hmmm…. That is an interesting one. 

I do not think requesting the file would count as opening it but “moving” or making some discrete update to the file might.
 

Try testing the Move File action in Zapier (I wouldn’t actually move the file though). 

When you set it up in Zapier, simply give it the id of the file and keep the folder the same. If it is in the main folder of your Google Drive simply type in root

If “moving” the file works then you would just need to set up a Zap to trigger with Schedule by Zapier.

Thank you both @MarjinVerdult and @GetUWired for your responses.

I have to try both. But I assume since I sometimes have to verify the login to CommCare for the refresh to happen just moving the file around probably won’t do the job.

But I will let you know if I was successful.

@Cora 

Any time! Looking forward to hearing the results! 

Hi @Cora, one other solution you could try is through Automator (Mac) or Windows Task Scheduler. This is of course not a Zapier solution and will run “locally”, i.e. on your computer but it might be a solution for your use case.

You could either automate it in such a way that the login will be remembered (advanced) or that it just simply opens the file in Excel or the Browser automatically at the set time and if login is required, you need to do that yourself. 

Anyway, keep us posted!

@Cora 
Just checking in to see if you still need help with this? 

@AndrewJDavison_Luhhu 

Thank you for checking in. I just saw the suggestion for the Windows Scheduler and set up one task for Monday as a test. I let you know if that worked.

The other recommendations didn’t bring the desired outcomes. I guess just sending a request and moving doesn’t automatically refreshes the data.