# Exceptions in deleting rows in a spreadsheet
**URL:** https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224
**Category:** Ask for Help
**Created:** [January 15, 2022, 2:20pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224 "2022-01-15T14:20:20Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 15, 2022, 2:20pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/1 "2022-01-15T14:20:20Z")
Continuing the issue that occurred when deleting rows with a script (discussion with @Darren_Murphy and @Uzo earlier), what if the deletion falls into an array range or the query formula includes an exception?
I’m seeing a strange occurrence where deletion with this script actually causes the addition of a new blank line. Did I miss something?
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [January 15, 2022, 2:35pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/2 "2022-01-15T14:35:58Z")
Can you show the script you are using, and an example of where it does the wrong thing?
Are there any error messages?
---
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 15, 2022, 2:43pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/3 "2022-01-15T14:43:14Z")
I tried both. Same result.
```auto
function remove_empty_timesheet_rows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Scoring");
var headers = sheet.getRange(1,3,1,sheet.getLastColumn()).getValues().shift();//method getRange(row, column, optNumRows, optNumColumns)
var timesheet_id_index = headers.indexOf('Name');
var user_id_index = headers.indexOf('Id Number');
var data = sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).getValues();
var row = sheet.getLastRow();
while (row > 2) {
var rec = data.pop();
var timesheet_id = rec[timesheet_id_index];
var user_id = rec[user_id_index];
if (timesheet_id == "" && user_id == "") {
console.log("deleting row %s", row);
sheet.deleteRow(row);
}
row--;
}
}
```
And get log after deleting multiple lines:
Service Spreadsheets failed while accessing document with id 1qaUtnkIRzffuLx1fYiFGQ-Nj01pCUBIGxTjESO\_P-i4.
remove\_empty\_timesheet\_rows
---
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 15, 2022, 2:55pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/4 "2022-01-15T14:55:52Z")
What I didn’t expect, this also happened to the script from uzo with a unique word **##delete**. And just deleting the last one to two rows creates a multiplication of the empty rows below it.
Or is there a leak from the array or query formula that causes this?
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [January 15, 2022, 3:01pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/5 "2022-01-15T15:01:45Z")
> [@Himaladin](#):
>
> Service Spreadsheets failed while accessing document with id 1qaUtnkIRzffuLx1fYiFGQ-Nj01pCUBIGxTjESO\_P-i4.
Does this happen every time you run it, or just intermittently?
I see that occasionally with some of my scripts. I think it’s just a transient sheets service error, and I’m not aware that anything can be done about it.
---
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 15, 2022, 3:09pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/6 "2022-01-15T15:09:01Z")
Seems to happen intermittently.
At first, before all this happened, I used the Uzo method. Seeing the emergence of blank lines (increasing 500 lines), I thought I could combine your script. At first it seems to work, but a new blank line appears again. I checked all my formulas and fixed them and still can’t stop adding newlines.
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [January 15, 2022, 3:12pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/7 "2022-01-15T15:12:22Z")
Sorry, I can’t explain that. I guess it must have something to do with your data.
I use that script that I gave you, and it works perfectly.
---
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 15, 2022, 3:17pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/8 "2022-01-15T15:17:51Z")
OK, I’ll try again and check all my formulas.
Indeed, I tried your previous formula successfully, but on a sheet that does not have an array formula.
I’ll let you know if I get to the source of the problem. Sorry to interrupt your weekend. Thank you.
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [January 15, 2022, 3:25pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/9 "2022-01-15T15:25:19Z")
I don’t think arrayformulas would cause any problems, because it’s only deleting rows - not writing any data to the sheet.
In fact, I use similar scripts with sheets that contain arrayformulas, and I’ve never had any problems.
---
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 15, 2022, 3:36pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/10 "2022-01-15T15:36:37Z")
Is it possible that the formula from my conditional formatting is not correct?
=$AV2\<56 (example custom formula)
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [January 15, 2022, 3:40pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/11 "2022-01-15T15:40:13Z")
I have no idea, what does the formula look like?
But, that might be a clue. Arrayformulas can certainly result in extra rows being created if they aren’t set up correctly. What do your arrayformulas look like?
---
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 15, 2022, 3:46pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/12 "2022-01-15T15:46:23Z")
Lots. It’s different if you delete it manually. Some of them:
=ARRAYFORMULA(IF(LEN($D$1:$D)=0, ,{“Total”;(0.1_R2:R)+(0.3_AA2:AA)+(0.1_AJ2:AJ)+(0.5_AS2:AS)}))
=ArrayFormula(IFERROR({“Rev3/Review 3 Score’s Average”;mmult(N(array\_constrain(AB2:AD,MATCH(2,1/($B$2:$B\<\>""),1),3)),sequence(columns(AB2:AD2),1)^0)/mmult(N(array\_constrain(if(AB2:AD\>0,1,0),MATCH(2,1/($B$2:$B\<\>""),1),5)),sequence(columns(AB2:AD2),1)^0)}))
=ARRAYFORMULA(IF($D$1:$D=0,"",{“Grade”;IFS($AX$2:$AX\>80,“A”, $AX$2:$AX\>=77,“A-”, $AX$2:$AX\>=74,“B+”,$AX$2:$AX\>=70,“B”,$AX$2:$AX\>=65,“B-”,$AX$2:$AX\>=61,“C+”,$AX$2:$AX\>=56,“C”,$AX$2:$AX\>=45,“D”,$AX$2:$AX\>=0,“E”)}))
And the conditional formatting formula as above.
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [January 15, 2022, 5:57pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/13 "2022-01-15T17:57:14Z")
When you are deleting rows, conditional formatting is adapting to that, that might create a problem
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [January 15, 2022, 6:20pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/14 "2022-01-15T18:20:55Z")
I wouldn’t be surprised if it’s your arrayformulas that are causing the problem.
Here is a test you can do: Add a handful of empty rows to the bottom of your sheet and watch what happens. If your arrayformulas are setup correctly, they shouldn’t add any data to those extra rows. If they do, then there’s a good chance that’s the cause of the problem. So you’d need to refactor them so they don’t do that. Or better still, get rid of them altogether and move the logic to Glide.
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [January 15, 2022, 7:46pm UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/15 "2022-01-15T19:46:37Z")
I also see that your array formula is bad for glide… it writes an empty value that Glide read as a new row and gives a row id… delete **`""`** , just leave **`,,`** without apostrophes.
`=ARRAYFORMULA(IF($D$1:$D=0,,{“Grade”;IFS($AX$2:$AX>80,“A”, $AX$2:$AX>=77,“A-”, $AX$2:$AX>=74,“B+”,$AX$2:$AX>=70,“B”,$AX$2:$AX>=65,“B-”,$AX$2:$AX>=61,“C+”,$AX$2:$AX>=56,“C”,$AX$2:$AX>=45,“D”,$AX$2:$AX>=0,“E”)}))`
check your other arrayformulas, for this issue
---
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 16, 2022, 3:22am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/16 "2022-01-16T03:22:08Z")
Dizzying thing. Did you @Darren_Murphy sleep late, sorry if I made you dizzy too.
@Uzo,
> [@Uzo](#):
>
> **`""`** , just leave **`,,`**
indeed previously without apostrophes, I replaced them and managed to prevent the conditional formatting from spreading. Now I return it without the apostrophe, with the additional formula \>1 instead of \>0 it works too.
I include a video below so that all can see clearly. Now I’m facing two problems:
1. Row ID regenerates continuously if there are empty rows (unless rows are pressed until they are not empty).
2. Symptoms of adding lines still occur when combining the two scripts, even though all formulas are deleted except for queries.
Any input?
> **[Screen Recording 2022-01-16 at 09.44.19.mp4](https://drive.google.com/file/d/1fp6dA_Gj-hxe_N6lDfFve5Ch1XnhSPNx/view?usp=sharing)**
>
> Google Drive file.
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [January 16, 2022, 3:28am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/17 "2022-01-16T03:28:15Z")
you deleted array formulas, but you did not clear these rows… so there are not empty… that’s why Glide is adding rows ID… or there is one more arrayformula that you missed
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [January 16, 2022, 3:37am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/18 "2022-01-16T03:37:51Z")
also, query function is like an array formula, it will write empty rows… you need to prevent that
---
### Author:  [@Himaladin](https://community.glideapps.com/u/Himaladin)
#### Post date: [January 16, 2022, 3:43am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/19 "2022-01-16T03:43:36Z")
I’ve tried it and the result is the same if both scripts are used. In the first part of the video, the script removing the blank lines cleared it too.
The only one is the query formula. I haven’t managed to limit it yet (how to do it?).
Row ID exists because I have a user-specific column.
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [January 16, 2022, 3:45am UTC](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224/20 "2022-01-16T03:45:28Z")
so the user-specific column is populated in Glide when there are no rows? is probably your query column, I stop using query formulas for glide apps… i use only vlookup… is harder but much faster results
[Next page](https://community.glideapps.com/t/exceptions-in-deleting-rows-in-a-spreadsheet/37224.md?page=2)