How to Auto-Refresh Google Sheets Formulas (Best Guide)

In this article, we will learn how to auto-refresh google sheets formulas.

Sometimes we work on live data, and in most cases, we need the data to be updated or refreshed automatically after a fixed time interval, we will see how to enable this. The functions like Google Finance have an auto-update feature but it works a little bit slow, and it auto-refresh the values every 20 minutes, but we need to auto-refresh the formulas quickly like every one minute. We will use a quick setting in google sheets to enable the auto-refresh in google sheets.

Auto-Refresh Google Sheets Formulas

As I have mentioned that some of the google sheets formulas and function use live data, such as google finance, utilizing these function are very common when we are working on financial or forecasting data, this function has a feature of auto-update the data in every 20 minutes, but 20 minute is a bit longer period in some situations, so we need it to work more quickly and we need some other feature that can auto-refresh any formulas or functions other than google finance too. So that’s why we need to learn how to auto-refresh google sheets formulas

• To auto-refresh the entire sheet every one minute or hour
• To get the latest values for live data function quickly and updated every time.

How to Auto-Refresh Google Sheets Formulas

In this section and onward we will practically see how to auto-refresh google sheets formulas, and we will also see when to do it and when to avoid it. Let’s see the below step-by-step procedure to understand and learn this auto-refresh feature in google sheets.

How to Auto-Refresh Google Sheets Formulas using Built-in option

We are going to learn how to use the google sheets built-in feature to auto-refresh the entire file every minute.

Follow the below steps:

Step 1

Sample data

Step 2

Go to File > Settings

Step 3

Now you two tabs, go to the second tab “Calculation”

Step 4

Click on the dropdown button under the “Recalculation” section

Step 5

Choose an option from three available options

On change: refresh data only when a change is made

On change and every minute: refresh data when a change is made and also every minute (if no changes are made)

On change and every hour: refresh data when a change is made and also every hour (if no changes are made)

Choose an option that best suits your preferences.

This is how you can set these options according to your need and it will auto-refresh google sheets formulas. This is how it works.

How to Auto-Refresh Google Sheets Formulas – Using App Script

Google sheets have a powerful app script in which you can make your custom functions and program any new feature. Below is an app script code that automatically refreshes specific cells of data, it simply picks the value and reassigns it and the refresh procedure takes place automatically. It only changes the value when the value is changed coming from the source location, if the value is not changed it will not show any change.

function refresh()

{

var price = 0;

price = SpreadsheetApp.getActiveSpreadsheet().getRange(“Sheet1!D1:D8”).getValue();

return price;

};

Explanation of Code

We have a function named “refresh” in the first line, following the syntax we have added parenthesis with the function name and created a function block by opening the curly bracket, then we have defined a variable that is “price” and set its value as 0, then in the next line we have reassigned the price value and did the following things, we got the active spreadsheet file, then we got the range along with the file name sheet1 and the range D1:D8, then a method “get value” to get the value of these cells, and at the end, this function is returning the variable price, which means this function will return a value when its executed successfully.

How to Add this Code to your Google Sheets File

Here we will see how to properly add this code to your google sheets file, the code is available above, you can simply copy it but we will see how to properly connect it with your file and how to set file name change, etc, properly and execute it on your spreadsheet.

Step 1

Copy the above code

Step 2

Go to your file, Click on Extensions > Apps Script

Step 3

Remove everything and paste the code

Step 4

In the second last line, replace the sheet name with your sheet name

Step 5

After the sheet name, replace the range with your date range

Step 6

Click on the save button

Step 7

Click on the Run button

Note:

You’re done. It will auto-refresh your data every time, and the changes will be made whenever the source data has got any change.

This is how to auto-refresh google sheets formulas using a simple app script code.

When to avoid the auto-refresh methods

You can use any of the auto-refresh methods in your google sheets file, but there are some important points to remember about this feature. If you have a lot of dependency in your data, you should avoid this feature, now dependency measure is done by you. You understand your data better than anyone. So, think if an auto-refresh can break your formulas, then you should avoid it.

Also, I will recommend you to use the built-in method that I described above for your personal and organization data, go for an app script when you have a very rare requirement, app scripts can have problems server problems sometimes execution problems, so if you have a small data and your data is not highly dependent to other data then you can use any method, keep in mind that you should have one backup copy of your data before using any of these methods. Also, keep in mind that these methods change your original data and you cannot get the original one if the data is refreshed various times by automatically working functions or built-in features.

Notes

FAQs

How to auto-refresh google sheets formulas?

Google sheets formulas and functions can be auto-refreshed using a simple built-in method in google sheets, you should have some meaningful data that has a possibility of change when the sheet is refreshed, for example, if you have a formula that adds A and B, there would be no change even after 100 years the result will be the same if you keep refreshing it without changing a value, so the best example is of the function google finance, that shows you live data fetched from google database. You can go to file > Settings, now you go to the second tab which is “calculation”, then from the drop-down button you can select any option forms on change, on change and every minute, and change and every hour. Just set these settings and clock on the save button and you’re done. This is how to auto-refresh google sheets formulas.

When should I use the auto-refresh feature?

You can use the auto-refresh feature whenever you need to have updated results instantly, google finance takes 20 minutes to auto-refresh data based on real-time changes made on the source data. But it can be refreshed every one minute to get the updated data after every one minute or one hour. You should use the auto-refresh feature when you have smaller data with less dependency on other data within the spreadsheet because the changing of the data every minute can seriously shuffle up your entire file and you can lose important data. Remember that the auto-refresh effect happens on the entire file, not on a specific cell or range of cells, so use it only when you need it and keep at least 1 backup copy of your original data for safety precautions.

Conclusion

Wrapping up how to auto-refresh google sheets formulas; we have learned how to auto-refresh google sheets formulas using a built-in method that allows us to auto-refresh data every one minute or every one hour. We have seen how quickly we can set up this method and auto-refresh data in our google sheets file, second method was using the google app script. That method is also very simple but, I would not recommend I have given a brief why I don’t recommend that method. So you should mostly work with the built-in google sheets method, which is highly secure and work efficiently without any errors or glitches. So, guys, that’s all from how to auto-refresh google sheets formulas.

I hope you find this article helpful and that you have learned what I intended to teach you through this tutorial. I will see you soon with another tutorial till then, take care. Share this with your friends and don’t forget to subscribe office demy blog for future updates. Thank you, keep learning with Office Demy.