Google Sheets API: Issue deleting rows with Array Formulas

I created an automation scenario in Integromat to delete a row in my sheet. It failed with an error code 400 and after several days working with Integretron they pointed out this error in the response:

Invalid requests[0].deleteRange: You cannot insert or delete cells over an array formula

Has anyone else encountered this type of error and were you able to work around it?

Maybe instead of deleting the row I need to overwrite a dummy value into the key field (user’s email).

That’s strange and should not happen. Do you mean Integromat here?

I think you can delete rows based on a condition with a Google Script instead.

I always confuse the name Integromat with the Integretron in Joshua Tree. :rofl:

I am not sure if Google Script would work, but wouldn’t both use a similar API call in the end?

Here’s a thread with some sample scripts for you to try.

Thanks, but I am not proficient in Apps Script (or have the time to learn it). That’s why I am starting with the Integromat route for prototyping :wink:

I think it’s fair to assume that Apps Script uses the same underlying API that Integromat uses to implement the delete row functionality and may have similar issues.

1 Like

Is Integromat attempting to delete the row that contains the arrayformula, or are you getting the error because it’s attempting to delete a row that doesn’t contain the arrayformula, but has a cell that is filled by the arrayformula?

The latter

I have encountered this before. My workaround for now is to do as you said overwrite a dummy value into the key field. Along with moving that row to an archive sheet just in case the row needs to be restored. Archive first, then update the row you want to delete.

1 Like