Simple Get action from Google Sheets is kicking my newbie behind!

Hey friends, I’ve searched the forum but can’t find anything that helps solve for my dilemma. I’ve got a simple spreadsheet that contains a list of questions. I have gone through Make Academy to the part where we are learning how to keep count of operations, so I’ve built the weather data get scenario and felt confident enough with Google Sheets to try this simple action.

All I want to do is schedule a scenario using Google Sheets and Outlook. The trigger (set to a day and time) pulls content from the spreadsheet I assigned in the module. I linked to the correct spreadsheet, I assigned the correct Sheet, and in the Cell box I placed the parameter “Random”.

It then sends that content (the bundle?) to the Outlook module which drafts and sends an email with the subject line and body text that I assigned.

I took a screenshot of the error and pasted it into this text box but I’ve never posted here before and have no idea if the image will actually render. I got a 400 Error in return though, saying “Invalid argument - unable to parse range”.

Apparently dropping “random” into the Cell box in the Sheets module isn’t how it’s done, and apparently randomly pulling the contents of a Google Sheets cell and dropping them into an email isn’t as easy as I assumed.

I did look into Data Stores because of one of the conversations I read from last year, but I haven’t done that module in the Academy yet and it’s nowhere near intuitive for someone who’s unfamiliar with databases and Make in general, so I’m thinking it still makes sense for me to figure out how to use Sheets and Outlook to make this happen.

Any ideas? If my screenshot doesn’t render I’m not sure how else to share an image, but I have to think this is easier than I’m making it. Thanks for any help.

And here is what I put into the Sheets module (I see the screenshot worked!)

I tried adding “get” to the Cell box but got the same error. I’m just playing around until people respond at this point. I also am talking with ChatGPT which is trying to get me to insert a tool that pulls all the rows, counts them, then randomly picks a number within the range of 1 and however many rows it counts, then sends the contents from that cell number to Outlook, but man that sounds like a whole lot of extra steps. Maybe I’m wrong though.

I spent 4 hours trying to get ChatGPT to walk me through this and it couldn’t figure it out. Is it really so hard to retrieve the contents of a random spreadsheet cell in Make?

Hi @davemontore

The random function generates random numbers between 0 and 1, that’s why you got the 400 error, This means random() can generate numbers like 0, 0.5, 0.7 etc and in your case it generated 0.74 which I believe cannot be the number of any cell.

In order to achieve this, you need to

  1. Search rows to get all the rows in the sheet

  2. Use an array aggregator to aggregate all items in the column you want to randomly pick from, eg column A

  3. Use a combination of get, random, and modulus operators to pick a random cell from that column.

Here’s a sample screenshot and results

Set up

Google sheet:

Iterator (Here i’m aggregating column A)

Getting random Cell

Results (First Run)

Result (Second Run)

I’m sorry but I have no programming background and am just trying to mimic what you have in the variable value box. Is there any way you can show me how you got that? I’m trying to trial and error it by typing into the box and hoping Make corrects me but it’s not happening.

I tried having the AI help me out by feeding your screenshot to ChatGPT then pasting the formula it gave me to Make AI but it didn’t help.

I also tried pasting your answer into Make AI and it gave me something very different:

This is as close as I was able to get it, but I can see there’s something off in the formula and it returned the same question three times.

Hi @davemontore , let’s hop on a call, let’s see if we can sort it out.

Truly appreciate the offer, Emmanuel!

But I zoomed out and realized it’s way easier to use a simple spreadsheet formula that selects a cell from column A at random and then have Make Get whatever is in cell B1 every 24 hours.

Stil working on the spreadsheet autorefresh script but that seems way easier than playing with aggregators in Make.

Thank you so much, though.

That’s great!…glad you found a working solution

Actually it turns out I didn’t, but I’m sure it’s only because I don’t understand how to format this stuff (I really did go through this part of Make Academy). I just want it to pull from Cell B1, any idea how I format that?

Clearly I’m making a mistake because it couldn’t parse the range again. Under the Cell box it says to enter the cell ID but I did that and it absolutely did not work.

1 Like

I stand corrected, typing B1 did work, I’d forgotten to save my scenario. Thank you so much again, really appreciate it!

2 Likes

Great lesson on biasing function>form!