Skip to main content

ive created a column in google sheets with a calculation from other manychat fields, but when a new row of data is inserted, obviously the calculated cell is empty. how to make a calculation and return the value to a manychat whatsapp message? 

I created an array formula in the google sheets header row. Then have copied the web app url and tested the request but error received

 


Hello ​@Mikeymike ,

Regarding the formula being deleted when a new row is added to the spreadsheet, one possible solution is to use two separate sheets — one exclusively for ManyChat to insert new rows, and another tab containing formulas that simply read from the first sheet.

I'll share a screenshot below to explain this more clearly.

As you can see, the first sheet is used only for ManyChat to create new rows:


Now, notice that in the second sheet, I use a combination of the INDIRECT and ROW functions. This essentially creates a mirror of the first sheet. Even if rows are added or deleted in the original sheet, the formulas in the second sheet are never removed.
Formula example:
=IF(INDIRECT("input_manychat!A"&ROW())="","",INDIRECT("input_manychat!A"&ROW()))
See screenshot:
 

As for the App Script, it seems it is not currently deployed. A simpler alternative, in case you're unable to fix your script code, is to use the native ManyChat and Google Sheets integration to retrieve the data directly from the second sheet.
When creating new records, make sure to also send the user ID. Then, you can retrieve the value already calculated by the formula — without needing any external request.


Thanks, but i used array in the header cell of the original sheet, (no need for duplication) and that applied a formula to the whole column below. The problem is now to insert the quote in a message.. see route below..a blank is returned. The lookup value is WhatsappID

 


send message is returning other fields values (that were whatapp entered), but quote, a figure i both manually entered on the spreadsheet (as well as trying as a array calculation) returns a blank. How to make manychat pickup quote? quote is setup on the lookup table

 


Hello ​@Mikeymike ,

One possible issue might be the data format. Your field in ManyChat called {Quote} is set to numeric. If the data from your spreadsheet is being imported as text, it may not save correctly. Try importing the data into a text-format field and see if that resolves the issue.


yep, text format in manychat displays the sheets calculated field!! BINGO! thanks


Reply