Data calculate in Data editor to Gsheets

Hello Glide community.

It is quite easy to retrieve data from Google sheets in the GLIDE data editor to calculate formulas, make relationships, etc.

However, is it possible to do the opposite, namely to recover calculated data from the glide data editor (therefore not visible in google sheets) in Google sheets?

Thank you
Ced285

One workaround for this is to use the “Columns” component in the form button, that would write values from the previous screen to your sheet.

Thank you for that answer. if I take the example of the form again, I would have to be able to record the validation date of the form in 2 different sheets … this does not seem possible to me, I only have the choice of one sheet?

Can you describe in more details your data flow in the app and what you want to record in the sheet? Thank you.

Well, I didn’t want to go into too much detail so as not to be confused …
I will try to be synthetic.

I have 2 sheets to manage climbing equipment.

1 sheet with all of the material, each material having a unique identifier: material

1 other sheet linked to a form which serves as a history for the verification. Historic

In the sheet which centralizes all the equipment, I made a relation thanks to the glide editor with the “historic” file and a rollup to determine the date of the last verification (special value in form > date) for each equipment of the material file.

Only, this value (last check) does not appear in my Gsheets because it remains in the Glide editor. I want to export to Word with this “material” sheet to automatically generate 1 sheet per material … and I need this date of last verification. It is present under glide but not in Gsheets … hence my first remark.

I explored the arrayformula function to go only through sheets, but I can’t get it to work with the maxif function. (maximum dates for each material identifier)

I hope I have been clear because it is not easy to explain everything, the project is quite complex …
Thank you

Here’s how I envision the solution: you can use a QUERY for this.

Let’s say in your historic sheet, equipment ID is in column A, all the dates are stored in column B.

In your material sheet, for the last date column you want to have, add:

=ARRAYFORMULA(IF(A2:A<>"",VLOOKUP(A2:A,SORT(Historic!A2:B,2,FALSE),2,FALSE),""))

This works in the way that you have the sort function automatically sort the Historic sheet by the date column. The Vlookup’s behavior is that it only returns the first match, hence the latest one.

Please try it for your case and let me know if I can help.

1 Like

thank you very much for your help, it works perfectly … you saved me a considerable time.
I added the function (IFERROR) if the ID is not yet in the history.
https://docs.google.com/spreadsheets/d/1H6x2CVUXb0a5ZEWRCgmjbGtDNBnBr5sGrV4iTLWikO8/edit?usp=sharing
Last little suggestion … Each check is carried out by a person …
From this formula, how to retrieve the entire content of the line (choose the elements or not to keep). In the case example, also recover the name of the inspector?

Thank you again for your help, making a verification and monitoring tool for security equipment with GLIDE would be great!

Ced

You can add an element to the VLOOKUP like this:

=ARRAYFORMULA(IF(A2:A<>"",VLOOKUP(A2:A,SORT(Historic!A2:C,2,FALSE),{2,3},FALSE),""))

The {2,3} is an array and it tells the formula to return column 2 and 3, not just 2 like the previous one.

1 Like

It works, Thank you very much for your help and your responsiveness!

1 Like

If you have anything else to ask feel free to comment here, have a nice weekend :smile:

1 Like

If it helps you to manage your work, I have written 2 posts about ARRAYFORMULA and QUERY and will publish one more about QUERY in the coming days.

2 Likes