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.

1. Appscript time-driven triggers
2. Server-side scheduled auto-refresh

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.

1. Select one or more file/folders on Google Drive™.
2. In the sidebar, click on the Drive Explorer Icon.

3. In the panel, select Open with Drive Explorer web app. You may have to wait a few seconds for this.

4. On the webpage opened, apply filters if required & check Export file details to a Google Sheet.
5. Input the Google Sheet™ URL and select the fields needed in the dropdown.
6. Check Set up auto-refresh in the Google Sheet.
7. Choose a refresh interval.
8. Click Set up auto-refresh.
9. In the page opened, authorize to complete the process.