1. The first step is to make the connection between Odoo and Google Spreadsheets working. Since it is not the subject of this forum entry, I assume the connection is done and the extraction of data in Google Spreadsheet works. 

 Since we have a formula for extracting data from the Odoo database, we now want to have updated data each time we open the spreadsheet or depending on a time-driven solution. 

The easiest way to do so is to refresh the formula extracting the data (so basically the “oe_browse”   or “oe_read_group”). One way to do it manually is to cut the formula and paste it in another cell or we can also insert a line above the formula. Both ways work but since we have to do it manually it is not efficient and totally not user friendly. 

2. The second step consists of implementing a solution to refresh the formula automatically. One way to do so is to change data in the formula. Indeed, if you go into your formula and modify the number of lines displayed for example (the last line of your formula) you will see that the formula is refreshing. So we know that by modifying a condition in the formula, the formula is refreshed and the extracted data is updated. 

So what we could do is to say in the formula that the lines displayed in the Spreadsheet must be the number displayed in the cell E1. 

“=oe_browse("stock.quant";"product_id location_id owner_id available_quantity";"";"";E1)”

Now we want that the data, appearing in the cell E1, to fluctuate from one number to another. This way the formula will be updated each time the fluctuation occurs.  

3. In order to have this fluctuation, we will write, via the script (I will explain later why), in cell B1 the function =now(). This function will give the date and the hours in the cell. 

In cell C1, we will just write “=B1” to display the hour of the day. 

In cell D1, we will use the function “=minute” to display the minutes of cell C1. 

Thanks to these formulas, we will be able to have a fluctuation by adding in Cell E1 the following formula 

“=IFS(ISEVEN(D1) = TRUE;1000;ISEVEN(D1) = FALSE;1001)”

This formula will display the number “1000” for each even number and “1001” for each odd number. This way we will have a fluctuating number each minute and these numbers (1000 & 10001) will represent the number of lines displayed in our formula extracting the data.

4. What we need now is to automatically update the formula (=now) in order to make the formula in cell E1 move from one number to another.  In order to do so we will add in the script of the Spreadsheet the following function. 

“

function trigger_cell(){

   SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data').getRange('B1').setValue(new Date()).setNumberFormat("MM/dd/yyyy");

}

“

A) Go to "Extensions"

B) Select "App script" 

C) Scroll down in the script, go on the last line and click on the "enter" button two times in order to have your cursor two lines after the last written line. 

D) Paste the function then save the script. 

E) Select the function to run (switch "_test" by "trigger cell") and Run the script. 

F) Once it is done and if you have no error message (you should not have an error message if you wrote nothing else in the script than the function), you should see in cell B1 the date of the day.

You do not need to understand how the function is written but you need to understand why we use the script to add the function “=now) in cell B1 instead of directly writing the formula in the cell. 

5. The reason is that we want to use the “Trigger option” available in Spreadsheet. A trigger is a way to refresh formulas depending on a condition (could be time driven or while opening the file for example). The thing is we can only use triggers on functions written in the script. This is why we created the function “now” instead of directly writing the formula “now” in the cell. 

So now we need to create a trigger. To do so, go back in the script (Apps Script), go on the left menu and click on the Timer logo. 

1. Select “Triggers”, create a new one.

2. Choose the function “Trigger cell” 

3. Leave “Head” in “which runs at deployment”

4. Select your event source, for example Time Driven 

5. Select type of time based trigger, for example Minutes timer

6. Select minute interval

6. Once everything is set, you will have your excel formula which extracts data from Odoo refreshed every minute thanks to the link with the cell E1. The cell D1 displaying the minutes will be refreshed every minute thanks to the link with the cell B1. The cell E1 will fluctuate from the number 1000 to 1001 each minute.

Here is the configuration without the explanation

B1: “now” function displayed via the script

C1: “=B1”

D1: “=minute(C1)

E1: “=IFS(ISEVEN(D1) = TRUE;1000;ISEVEN(D1) = FALSE;1001)”

The formula extracting data (just an example to show you the link): 

=oe_browse("stock.quant";"location_id product_id available_quantity";"";"";E1)