How to store the data output of a query as a value

[mandy]

i just performed a query to capture data from sheet A to B. i would like the data output from the query to be then stored as a value, such that when my data source (sheet A) changes, it does not affect the data i have already retrieved through my query (sheet B). any ideas how i can do this?

i am using the script editor daily tool in gsheets & the data source i have refreshes daily to give me the past 31 days data. as such, once the month is over, my query no longer captures my older data in sheet B. i would like the older outputs to maintain as a value in sheet B, now it is showing 0 due to blank output as the older day’s data is no longer available in the data source.

[Tanaike]

Unfortunately, from your question, I cannot understand your actual situation. But, about `i would like the data output from the query to be then stored as a value`, I think that this can be achieved Google Apps Script.

[mandy]

hello Tanaike, 

apologies if i wasn't clear in my question. because my script is fetching data from a sheet which always contains the latest 31 days data set. so for example, if today is 24 nov, i will have data from 24 oct-24 nov. however, once it refreshes tomorrow, i will end up losing the data from 24 oct. 

i am asking if there is a way for me to store this 24 oct data without losing it when the main data sheet refreshes? 

hope i've been clear. 

[Tanaike]

Thank you for replying. I think that the conversion from the formulas to the values can be achieved by Google Apps Script. For example, at first, retrieve the values which are not the formulas, and copy the retrieved values to the other sheet. By this, even when the formula is refreshed, the copied values are not changed.

[mandy]

thanks for your reply! oh ok, cause i'm trying to see if there is a workaround from this copying action. as now i use google apps script 'daily' to retrieve these data into a new sheet, but the data will be 0 once the main sheet refreshes. was thinking if there is any other way instead of copying the retrieved values all the time to stop it from disappearing? 

[Tanaike]

Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I cannot imagine your situation from `but the data will be 0 once the main sheet refreshes.`. Can I ask you about the detail of it?

[adroi...@gmail.com]

I'm not sure if I am 100% understanding the situation correctly. It seems like you have a query that moves data from one sheet to another, but loses data as each day passes. Instead of losing that data, you would like to keep an historical record of all of the data that has already happened and is not currently within this 31 day period. I think the daily google apps script trigger is a good start. I think the steps should go something like this assuming the dates are in chronological order in your sheet, and assuming row 1 is the header row.

1. Script run's once a day

2. Function gets the sheet A's second row

3. function grabs that row's values

4. function appends values to sheet B

These values will remain in the sheet regardless of the query change.

[Tanaike]

Thank you for replying. "getValues" can be retrieved the cell value instead of the formulas. So I think that your flow can be achieved using the time-driven trigger.

[mandy]

Hello both,

thank you so much for all your advice! i will try it again and hopefully it will work this time.

Thank you!!