Reference previous or next row in Google Sheets


I am trying to figure out how to return data from a different row based on data the user provides from a starting row. Basically, what I want to happen is the user asks Alexa “What movie comes before/after {movie title}?” Alexa will respond with the title of the movie in the row before or after the row containing the title the user asked for. So if the user asked for a title that is in cell B5 then Alexa would return the title that is in either B4 or B6 depending on whether the user said before or after.

I have tried using the Set block to set this formula (=ARRAYFORMULA(OFFSET({movie title},-1,0,1,1))) as a variable ({previous}) and then use that varible ({previous}) as the Mapping Output but it keeps only returning the same title the user asked for, not the one before.

Any help would be greatly appreciated!

I don’t think you can set Google Sheets’ formulas in set block. you can set only strings or numbers in Set block, I think. but this is kind of interesting.

Anyway, this is just my idea.

  • B5 has a movie name. C5 has a fomula to get next movie name, means B6.
  • Then, map B5 to variable “cur_movie”, B6 to variable “next_movie”.

There might be smarter way to do this.

Thanks for the suggestion kun432! I’ll give that a try. It makes sense that the formula would have to stay on the Sheets side. This definitely seems like it could lead me down the right path. Thanks again.

So turned out the actual formula I needed was =INDEX(B1:B23,MATCH(G2,B1:B23,0)-1,1) and then mapping the Voiceflow Integration block to the field where that formula was. Works perfectly now. So even though I didn’t do it exactly as kun432 suggested it was his suggestion that led me to the solution I was looking for. So thanks again kun432.

1 Like

Hey, I am new to voiceflow. But I have done something which may be helpful for your question…

  1. Use Integration block to link to Google Sheets
  2. created a variable called “row_number”
  3. used a set block to set “row_number=<some value”>
  4. In Google Sheet integration block mapped “Row Number” = {row_number}
  5. inserted a “code block” with a javascript code “row_number +=1;” for next row.

Hope this helps…

1 Like

@Steve I’ve got the same issue as you had. What did you do to map the Voiceflow Integration block to the cell that contains the formula? Thanks!