Hey guys, Im hoping you can help me here…
I have a scenario that is triggered from a voiceflow API call (containing variables from voiceflow), and almost immediately after I need to clear cells of a monetary amount on my google sheet, and then input a new monetary amount in said cells further down the scenario. I am having a problem however, as the “clear a cell” function doesnt just remove the value, it also clears it of its formatting, i.e. the cell is set to “currency”, but after the “clear a cell” function runs, the cell is set to automatic and it removes the currency symbol, screwing up my equations within the google sheet.
I need to clear the values, but keep the “currency” formatting.
Has anyone else came across this? Any ideas on how to get around it?
I think I can do this by running a scrip/function within the google sheet (I have the script in there already), but I dont know how to get make to trigger the function within google sheets!?
Can anyone help me?
Thanks inn advance guys
1 Like
Hello @Lee_Webster and welcome to the Make Community!
This is interesting because Clear a Cell uses this endpoint, which clearly states:
Clears values from a spreadsheet. The caller must specify the spreadsheet ID and range. Only values are cleared – all other properties of the cell (such as formatting, data validation, etc…) are kept.
I can offer a suggestion: why not just update the cell to 0 then update it again later down the scenario?
2 Likes
I can’t do this, because…
The invoice that I am creating has 2 sections. A “primary work” section and a “secondary work” section just underneath.
However, sometimes there is no secondary work carried out, so in this case, I want the secondary work section to be completely blank.
The way the invoice works, is that it’s a Google sheet that has all the details filled in, and then that Google sheet is printed to PDF and emailed to the customer (all automated). So when I start a new invoice creation, the invoice still has the details on it from the last invoice creation, and if the previous invoice creation has details in the secondary work, and this invoice does not contain any secondary work, then the secondary work needs to be cleared.
The problem arises when I go to make another invoice AFTER the invoice template has had the secondary work CLEARED. As this new invoice may need to include secondary work BUT since the cells were CLEARED in the last operation, the monetary value in the secondary work cost cell, is no longer set to currency.
I need to keep the cell set to currency.
Is there a way to format a cell into “currency” in make.com? This would work.
Lee
Yes, you can, but you’d have to find the endpoint to use and call it with a Make an API Call module.
However, given what you’ve just said, why not just copy the blank Google Sheet, fill it in, PDF it, delete it? Seems easier this way, no?
No I can’t do this either, as the customer info is inserted into the invoice in a different scenario (again from Voiceflow).
Could you expand a little on this mate:
“Yes, you can, but you’d have to find the endpoint to use and call it with a Make an API Call module.”
I’m not sure what you mean there.
We can hop on a quick call at some point if you like?
Thanks in advance pal
Lee
I still think you’d be better off trying to copy the spreadsheet, add customer info, pass the new spreadsheet info on to the next scenario somehow to continue filling in the invoice details.
You would use Google Sheets Make an API Call module, where you specify your own API endpoint, query parameters, additional headers, body, etc…
It’s the batchUpdate endpoint, but you’d need to find the right code to format the cell.
I just tested Clear a Cell and it did not wipe out my formatting.
Another thing occurred to me, instead of Clear a Cell, try Update a Cell and just update it with value of {{emptystring}}?
It would look like this:
Might be less damaging to the cell and still accomplish your goal.
Ahhh, the empty string may work!! I never know I could fill it with an empty string. Will this leave the cell completely blank?
It did in my tests. But again, Clear a Cell worked for me too without clearing the formatting so your results may vary with this too!
Hey @Lee_Webster
To run the script in make.com, you can refer to this post here. To help you with the cell formatting issue without using script, we would like to see the screenshot of the column data.
Regards,
Msquare Automation - Gold Partner of Make
Book a Free Consultation | Connect Live
Explore our YouTube Channel for valuable insights and updates!
Really? Wow that’s strange. Is it possible that it’s because my cells are merged? And I have a range of cells in the “clear cell” module rather than a single cell?
Here is a video link explaining everything. This will give you a better idea of the problem I am having.
Thanks
Lee
Hi @Lee_Webster
When I tested, it doesn’t happen with me. There can be a bug due to which you are facing the issue.
However, instead of clearing a cell modules, you can use if() statement to resolve the issue and it will save some operations also.
Please see this attached loom for better explanation.
Regards,
Msquare Automation - Gold Partner of Make
Book a Free Consultation | Connect Live
Explore our YouTube Channel for valuable insights and updates!
Ah guys, thanks for your all your help, but it was a silly mistake from myself 
my module that inserts the monetary value into the cell that I was having a problem with, was set to insert the data/variable as RAW. Once I noticed this, and changed it to USER INPUT, everything worked fine.
When it was set to RAW it was putting a coma in before the monetary amount, breaking it’s link of being a currency i.e. £250 was being inserted as '250
Such a silly mistake by myself.
Thanks for all your efforts anyway folks.
Awesome community.
Lee
2 Likes
