How to retrieve data after duplicating and relation in Google sheets

[Galerie]

Hello the Glide Community!

I am sending this message since I need some help. I face a problem between Glide and Google sheets. To explain more my issue, I am using Stripe and I duplicated the App : Sales sheet to a separate sheet called Sales by using this formula ={‘App: Sales’!A:Z}

From glide, I did some relation and lookup in this separate sheet called Sales. However I cannot see the data in Google sheets.

I need these data for sending zaps with zapier. Is anyone may help me please ?
Or suggest me solutions ?

Thanks in advance,

Regards

[ThinhDinh]

The relation & lookup data columns are Glide-calculated columns so you won’t see them in the sheet.

To be able to have that value in your sheet, you should create a vlookup arrayformula.

[Galerie]

Thanks for your response !
@ThinhDinh Could you please tell me how may I create the vlookup arrayformula ?

Regards

[Uzo]

to see data from glide tables or user specific columns, you have to write them to google sheets using any action with set columns.

[Galerie]

Thanks for your response @Uzo could you please give an example ? or tell me how I can do this ?

[Uzo]

. .

[ThinhDinh]

You can read more about arrayformula here.

Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

Hi, Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula. But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job.

1/ Definition

Arrayformula is defined as “a range, mathematical expression using one cell range …

[Galerie]

Hello,

I am not sure that these answers above may fix the issue.
I cannot see the column created from glide in Google Sheets.
Please see the screenshots.

Could you please see and advise ?

Column created in glide

Column not visible in google sheets

Regards

[Jeff_Hager]

Glide columns, such as relation, lookup, math, template, etc. Will not be visible in a google sheet. They are columns that are calculated on the fly, and actually are calculated on each individual user’s device. They are not columns of data in the traditional sense. What you see in those columns is being calculated in real time.
Those values are not stored anywhere, and because of that, none of the values from those glide computed columns will appear in a google sheet.

What is being mentioned in the previous posts is that you need to create separate formulas in your Google sheets if you need those same values in your Google sheet

[Galerie]

Sure I got it thanks @Jeff_Hager @ThinhDinh !
I am trying to use vlookup to retrieve data from a sheets but I need some help for the syntax of formula.

I put an example of my google sheets. In my case I would like to display
Vendor, Price, TAX, Commission, Price final in App : Sales sheets from Products sheet. In both sheets I have ID product to do the link. Letting you know my data come at the end of the sheets.

Vlookup

Could you please help me to build the formula ?

Regards

[ThinhDinh]

Can you share the editing rights to ariesarsenal@gmail.com? I can only view as of now.

[Galerie]

Sure @ThinhDinh I shared the editing rights to ariesarsenal@gmail.com

[Galerie]

Also I shared the editing rights to everyone

[ThinhDinh]

It has been done for you. The arrayformula takes the product ID in column D, looks to the Products sheet and returns column D, E, F, G through a temporary data table containing columns N, D, E, F, G of the Products sheet.

[Galerie]

Thanks @ThinhDinh for your quick help !
just to understand what does that mean {2\3\4\5} ?

Also do you know why when there are a formula in the sheet, the rows come at the end of the sheets after been added from glide ? Could you please tell how the rows can come at the beginning ?

[ThinhDinh]

The temporary data table goes N, D, E, F, G. I want to return D, E, F, G and their order is 2nd, 3rd, 4th, 5th.

When you use arrayformulas, you must delete all empty rows, because weirdly the Google API doesn’t see those as empty rows and writes the new rows at the end of the Sheet.

[Galerie]

Thank you a lot !!

So I have to delete all empty rows every time or once ?

[ThinhDinh]

You can just delete them all now, then the Google API will just add one row at a time at the bottom.