It seems I stuck with my first VF project. It is a very simple Google sheet integration. User asks for a recipe with tomato, Google sheet lookup column Ingredient and find the first recipe with tomato, and then shares all related information.
The problem is when a user wants another recipe. Here ideally I would like to return another recipe with tomato - a random one. I see that in google block I can indicate a new column, but this won’t work in case of base it is big.
Can anybody advise how I can do random suggestion based on slot value? Not just any random suggestion but based on user input?
I am not a developer, but a curious designer) Thank you!
I also looked if I can do something in the Google sheet itself. I used formula =SORTN(FILTER(B$1:B,A$1:A=A2),1,0,randarray(COUNTA(FILTER(B$1:B,A$1:A=A2)),1),TRUE) to generate a random output based on condition. But here there are two questions:
1 Not sure that API will be getting a random read each time
2 I have several colums which are based on the selected variable. So user says - tomato , I return a random recipe. User says, OK - I need to read out other columns. How I can attach other columns to this random output?
can define only one condition and always return same, or retrieve randomly without condition.
that means If you wanna retrieve data by a key and also by random, you should do this in other ways such as:
use google sheet functions like you did.
use custom API to get whole data and handle its data by writing some codes with code block.
If you are not familiar with coding, I recommend using Google Sheet functions although I’m not so familiar with those and it might be a little complicated.
Yes, it is a bit of a pickle. I can share what Andrew from VF suggested, but I don’t understand his suggestion.
I put together a small example that will allow you to retrieve random numbers within a certain range.
Drag in a set block and create a variable called “maxNumber”, set this to the maximum number you will have in your spreadsheet
Create a variable called “random”
Drag in a custom code step and paste this code:
random = Math.floor(Math.random() * maxNumber) + 1
4. Enter {random} in the “with settings” section that you sent a screenshot of before, instead of {ingredient}
This will provide you with a random number everytime.
Here is a screenshot of what it looks like on my side:
No. I am not coding so that is why all these questions)) I created a max number and also added custom code, but not sure what i do with my ingredient request.
If I want to dramatically simplify the whole thing and just return random items from Google sheet - without any request from user - is it possible?
random number might be required if you use code block, I think.
Anyway,
yes. With Settings in Google Sheets Block, just choose any column and not fill in values, it goes random.
Airtable is more like database and you can get multiple records with a condtion. But Airtable itself does not have a feature to return randomly. So it will be like these:
use api block to retrieve multiple records with a condtion from Airtable, and put those into a variable.
use code block to
count the number of records returned from Airtable.
using the number of records as maximum number, generate random number.
using random number as index, get a record from the variable.
At least, you will need write some codes, I think.
Thank you so much. Since I am doing this all with learning purposes - learning more about voice design, I will just simplify my skill dramatically and will return random recipes. I am not planning to learn to code, not my thing)))
