# Delete blank row with macro

**URL:** <https://community.glideapps.com/t/delete-blank-row-with-macro/36723>\
**Category:** Ask for Help\
**Created:** [January 5, 2022, 1:15pm UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723 "2022-01-05T13:15:02Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 5, 2022, 1:15pm UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/1 "2022-01-05T13:15:02Z")

</div>

I would like to inquire about the possibility of deleting blank rows in a spreadsheet with a macro (video below) with a trigger from Glide, and how?

[Delete Empty Rows (Blank Rows)](https://youtu.be/ND_3S9BtOUM)

Thanks in advance and happy year.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 5, 2022, 1:40pm UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/2 "2022-01-05T13:40:21Z")

</div>

Just out of curiosity, why would you want to trigger this from Glide?

Wouldn’t it make more sense to just use a timed trigger to run a script periodically (say once a day) and do a sweep of all sheets to clean out any blank rows?

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 5, 2022, 1:57pm UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/3 "2022-01-05T13:57:35Z")

</div>

Hi Darren,  
Actually what I need is only if at the time someone deletes a row in Glide only. I have a formula that doesn’t work if a blank row occurs in the middle. Besides that, I also publish a spreadsheet, of course, it won’t be beautiful if there are blank gaps between the rows.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 5, 2022, 2:20pm UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/4 "2022-01-05T14:20:51Z")

</div>

Okay.

I think I’d still just run something as a timed trigger, but maybe more frequently - maybe once every 5 minutes. If you really want to trigger it from Glide, there are a couple of ways to approach it. But what I’d suggest is rather than using the Glide delete row action, instead write a value to a column (or clear the value in a column) that will act to signal to your script that the row should be deleted.

Here is an example script that I’ve used recently, that you might be able to adapt.

```auto
function remove_empty_timesheet_rows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Timesheet');
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().shift();
  var timesheet_id_index = headers.indexOf('Timesheet ID');
  var user_id_index = headers.indexOf('basic/User ID');
  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--;
  }
}

```

The above example looks at the values in two specific columns. If both are empty, then it deletes the row.  
One _very_ important thing to be aware of when deleting rows using a script like the one above is that you must _ALWAYS_ start from the bottom of the sheet and work up. This is because the script starts by reading the entire sheet into memory, and then works through it row by row. If you started from the top, then as soon as you delete the first row, all of the row indexes will change, and if it finds a second row to delete then the _wrong_ row will be deleted.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 5, 2022, 2:31pm UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/5 "2022-01-05T14:31:58Z")

</div>

Looks like it’s fit for purpose but I need time to digest/try this script first. Give me time to report the results.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [January 5, 2022, 5:40pm UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/6 "2022-01-05T17:40:23Z")

</div>

Check out this code…

> **[Code BOOK](https://code-book.glideapp.io/dl/ewAiAHQAIgA6ADQALAAiAHMAIgA6ACIAYwBsAGEAcwBzAC0AUwBjAHIAaQBwAHQAcwAiACwAIgByACIAOgAiAFgAOABoAC4ATQBOAHgAbABSAFcAQwAzAFgAOQAtAEgAdgBvAGEAWgB1AEEAIgAsACIAbgAiADoAIgBTAGMAcgBpAHAAdAAiAH0A)**
>
> share cool codes, scripts and links

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 6, 2022, 4:38am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/7 "2022-01-06T04:38:25Z")

</div>

@Darren_Murphy, your script works very nice. One more question, how to run this script for multiple sheets. Looks like I need to learn java in depth. 😔  
Thank You.

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 6, 2022, 4:38am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/8 "2022-01-06T04:38:41Z")

</div>

I also tried the Code Book method by adding a column in the spreadsheet that contains a unique word, unfortunately, I didn’t succeed. Did I miss something?

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [January 6, 2022, 4:48am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/9 "2022-01-06T04:48:36Z")

</div>

did you add on change trigger? and unique trigger word is exactly the same? and sheet name changed to your sheet name ?

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 6, 2022, 4:50am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/10 "2022-01-06T04:50:57Z")

</div>

I used the run button to try on copy sheets.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [January 6, 2022, 4:52am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/11 "2022-01-06T04:52:41Z")

</div>

run will not do it… it has to be triggered and you need to write delete word to that column … that will trigger the script

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 6, 2022, 4:54am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/12 "2022-01-06T04:54:59Z")

</div>

Ok, I’ll try on the original sheet and use Glide to trigger it.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [January 6, 2022, 4:55am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/13 "2022-01-06T04:55:38Z")

</div>

> [@Himaladin](#):
>
> One more question, how to run this script for multiple sheets. Looks like I need to learn java in depth

Do you want to run it for _all_ sheets, or just a specific list of sheets?

If for all sheets, you can get a list of all sheets using `SpreadsheetApp.getActiveSpreadsheet().getSheets()`, and then pass each sheet to the function in a loop. If for a specific list of sheets, you can do the same thing except start by defining an array of sheets.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [January 6, 2022, 4:55am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/14 "2022-01-06T04:55:46Z")

</div>

you can use glide or just write directly in gs sheet

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 6, 2022, 5:00am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/15 "2022-01-06T05:00:05Z")

</div>

I only need two sheets out of many, which is fast. As far as I know, if there is a lot of activity in GS it will slow down its sync with Glide.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [January 6, 2022, 5:07am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/16 "2022-01-06T05:07:48Z")

</div>

this code will delete row in any sheet:

```auto
// (c) StructureArt DIGITAL
  
function deleteanyRow() {
 
  var valueactivecell =SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell().getValue();

  if(valueactivecell == "###delete"){ // this is a special word to trigger this function, you can change it to your own

  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() 
   var row = s.getActiveRange().getRow()
   
   if(row > 1){ // prevent from deleting column title
    
     s.deleteRow(row);
    
    }
  }
}

```

simply paste that code in the script editor and save it…  
create on change trigger for function deleteanyRow … save

paste ###delete to any row in any sheet… see magic happens!

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 6, 2022, 6:43am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/17 "2022-01-06T06:43:13Z")

</div>

I need more time to test your script @Uzo. Meanwhile, trouble occurred during testing. The script duplicated into GS and broke the references from my formula array and affected synchronization in Glide. I need to rearrange the columns in Glide.  
But anyway I’m very grateful for your intention to share, and this script should definitely work well.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [January 6, 2022, 6:44am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/18 "2022-01-06T06:44:41Z")

</div>

my script duplicated???

---

<div class="post-metadata">

**Author:** ![Himaladin](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/himaladin/32/71023_2.png) [@Himaladin](https://community.glideapps.com/u/Himaladin)\
**Post date:** [January 6, 2022, 6:48am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/19 "2022-01-06T06:48:25Z")

</div>

I mean self-copying into GS, not deleting rows. I don’t know why it happened.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [January 6, 2022, 6:51am UTC](https://community.glideapps.com/t/delete-blank-row-with-macro/36723/20 "2022-01-06T06:51:41Z")

</div>

i have no idea what you mean by self-copy, there is only one action in the script… delete row

[Next page](https://community.glideapps.com/t/delete-blank-row-with-macro/36723.md?page=2)