Query user data on reviews form based on product sheet

Ask for Help
1

I am trying to get user preferences from user profile to the reviews sheet for products. The form that adds data to reviews sheet does populates columns from products but not user sheet. I can use the lookup component in reviews sheet for each column on user profile but my concern is that it doesn’t sync back to sheets or create an array. Ultimate goal is to use images that will display user preferences on users review.

I tried query function: =QUERY(definedrange,“select P, Q, R, S WHERE B=‘unique-email@gmail.com’”) . It works but I can’t replace the value ‘unique-email@gmail.com’ with the cell value . Has anyone been successful in using QUERY or FILTER functions?

Any help is appreciated.

2

Hi, maybe a part of the answer: did you try to include the syntax ’ " & reference cell & " '

example: ={arrayformula (if(isblank(A2:A);""; query ( Feuille#1 ; "SELECT B WHERE A = '"& A2:A &" ’ " )))}

nb: mine doesn’t work neither but for another reason :slight_smile:

1 Like
3

In this case QUERY won’t work with ARRAYFORMULA. It will always return the result for A2.

1 Like
4

@AyS_0908: Thanks for responding. Yes, I tried it but may be didn’t format it correctly. I am still a nb with sheets formula. Would you mind telling me if this is the correct way:

=QUERY(definedrange,“select P, Q, R, S WHERE B=‘&A2&’”)

If yes, it doesn’t work.

5

Exactly the issue I have for my case.
But for the one of @S_C? (ie. without arrayformula)

6

Can we use QUERY and FILTER function combined. Assuming both work with arrayformula.

7

Use the script to copy it down here, if he has already found the right outcome for his case.

8

I am also totally newbie on this, but in your above formula, you may try with the end written as follow: ‘"&A2&"’ (you must have " + ’ on each side)

1 Like
9

If you’re ok with showing me the data structure you’re having in place I would appreciate it. Want to have a live look to provide better recommendations if I can.

10

Thanks :smiley: I will correct that. Either way I will need to join strings sooner or later.

11

Tutorial seems very helpful. Thanks for sharing the link. I will go through it.

Sure thing! Can I send you a PM though?

1 Like
12

Yes, send me a personal message.

1 Like
13

Are these values allowed to change on the reviews if a user changes preferences, or are set once the review is written?

I’m thinking template columns either on the products sheet or the reviews sheet, that pull values from the user profiles sheet, depending on what you are trying to do.

1 Like
14

@Jeff_Hager: I am okay with either of the solutions. I will be using these for relations with another sheet.

I noticed another post in which you had recommended this and it worked for the person who had asked the question. However, I wasn’t able to reproduce that. Specifically, I don’t know how to use template column to pull columns from another sheet.

Can you share a resource/video?

15

Thanks so much for your help @ThinhDinh.

The formula you used with ARRAYFORMULA and VLOOKUP works and doesn’t .
It pulls the data in reviews sheet exactly the way I want. However, two issues exist- 1. New reviews don’t sync back to Google sheets 2. Glide shows the reviews in its own database and app one minute and next minute those are gone. It has happened 4 times by now. I am guessing that Glide’s system doesn’t like VLOOKUP or there is a sync issue. I will try again an let you know.

Either way your help is much appreciated .

16

I’m picturing values that you have in the user profile sheet. When creating template columns in other sheets, you can include values from the user profile. These values you could possibly pass into a form, or simply just use them for display in the app.

Screenshot_20200704-144919
Screenshot_20200704-144919796×1695 144 KB

1 Like
17

This is interesting. I am doing exactly that and for some reason the user sheet or other sheets are not visible as choice in my template menu.
Do you have unique ID row in your original sheet (non-user sheet)? I have that. Could that have anything to do with it?

18

Have configured your app to use user profiles?

https://docs.glideapps.com/all/reference/privacy-and-per-user-data/user-profiles

1 Like
19

Yes. I checked it again.

20

Why is this happening? Did you catch it through a form or something else?

This is strange and I might need some screenshots or screen recording.

1 Like