Google sheet integration _random output based on slot value

Hi guys,

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?

Google Sheet block…

  • can return only one row.
  • 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.

1 Like

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.

  1. Drag in a set block and create a variable called “maxNumber”, set this to the maximum number you will have in your spreadsheet
  2. Create a variable called “random”
  3. 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:

on your end you will not have the speak block at the end, I only did that to make sure the {random} variable was working properly

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?

Also, can it work with airtable? can it return random value based on user utterance?

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.

s 533

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)))