Schedule Automatic Refresh of the Sheet Data
When your Google Sheet is a data source of something, it can be quite useful to update the data in the background automatically.
For instance, let us take an example of a Data Studio dashboard connected to a Google Sheet. Let us say the dashboard displays a list of images from your Google Drive by leveraging the Preview Link column listed by Drive Explorer. You can add new images to your dashboard just by uploading files to your Google Drive, without doing anything manually in your Google Sheet.
There are two ways you can schedule auto refresh of the data.
Appscript time-driven triggers
This method utilizes the appscript APIs to refresh your sheet periodically. It is a premium feature and requires you to have a premium license to use.
To enable scheduled auto update, list the file details on a Google Sheet and Select Extensions → Drive Explorer → Configuration, check "Schedule refresh in the background", in the dropdown select one of 1 / 2 / 5 / 10 / 24 hours and click on Save.
Now, the data will automatically update in a recurring interval set by you.
Limitation
Google appscript has a 6 minute limit on the execution time of appscript triggers. Hence, if you have a lot of files to list or if the files are deeply nested inside the folder, the execution time will easily exceed 6 minutes. In that case, the file list will be incomplete.
Server-side scheduled auto-refresh
In this method, the file details are fetched securely in a Drive Explorer server and the data is pushed to a Google Sheet. Premium plans starting from Standard have access to this feature.
Based on your plan, you can set up auto update of file details every 1 / 12 / 24 hours. Unlike the appscript time-driven trigger, it doesn't have any limit on the execution time hence it lists all the files.
- Select one or more file/folders on Google Drive.
- Right-click, select Open with → Drive Explorer.
- On the webpage opened, apply filters if required & check Export file details to a Google Sheet.
- Input the Google Sheet URL and select the fields needed in the dropdown.
- Check Set up auto-refresh in the Google Sheet.
- Choose a refresh interval.
- Click Set up auto-refresh.
- In the page opened, authorize to complete the process.
