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.
