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?
Can you show the script you are using, and an example of where it does the wrong thing?
Are there any error messages?
I tried both. Same result.
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
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?
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.
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.
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.
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.
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.
Is it possible that the formula from my conditional formatting is not correct?
=$AV2<56 (example custom formula)
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?
Lots. It’s different if you delete it manually. Some of them:
=ARRAYFORMULA(IF(LEN($D$1:$D)=0, ,{“Total”;(0.1R2:R)+(0.3AA2:AA)+(0.1AJ2:AJ)+(0.5AS2: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.
When you are deleting rows, conditional formatting is adapting to that, that might create a problem
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.
1 Like
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
Dizzying thing. Did you @Darren_Murphy sleep late, sorry if I made you dizzy too.
@Uzo,
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:
- Row ID regenerates continuously if there are empty rows (unless rows are pressed until they are not empty).
- Symptoms of adding lines still occur when combining the two scripts, even though all formulas are deleted except for queries.
Any input?
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
also, query function is like an array formula, it will write empty rows… you need to prevent that
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.
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