Google sheets script Auto-run a script and add function in the menu bar

Question

I use a script that allows me to delete the empty rows in the middle of my table and also constantly adds rows at the bottom so that I can keep filling it. Everything works perfectly. I just want the script to run automatically if I delete or add a value in the "C" column. And also, if possible, add a function in my menu bar with onOpen(e) in case the script doesn't execute and I have to run it manually. This is my Sheets:

My Sheets

This is the script:

function removeEmpty() {
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients');
  const empty_rows = [];
  const lastRow = sh.getLastRow()
  const data = sh.getRange("C6:G" + lastRow).getValues();
  for (var i in data) if (data[i].join('') == '') empty_rows.push(+i + 6);
  empty_rows.reverse().forEach(x => sh.deleteRow(x));
  sh.insertRowsAfter(lastRow - empty_rows.length, 5)

  var rng = sh.getRange('A6:Z6')
  rng.copyTo(sh.getRange('A' + (lastRow - empty_rows.length + 1) + ':Z' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

  // H-I-J-K-L
  var rng = sh.getRange('H' + (lastRow - empty_rows.length) + ':L' + (lastRow - empty_rows.length))
  rng.copyTo(sh.getRange('H' + (lastRow - empty_rows.length + 1) + ':L' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  // O
  var rng = sh.getRange('O' + (lastRow - empty_rows.length) + ':O' + (lastRow - empty_rows.length))
  rng.copyTo(sh.getRange('O' + (lastRow - empty_rows.length + 1) + ':O' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  // Q-R-S-T-U
  var rng = sh.getRange('Q' + (lastRow - empty_rows.length) + ':U' + (lastRow - empty_rows.length))
  rng.copyTo(sh.getRange('Q' + (lastRow - empty_rows.length + 1) + ':U' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  // X-Y 
  var rng = sh.getRange('X' + (lastRow - empty_rows.length) + ':Y' + (lastRow - empty_rows.length))
  rng.copyTo(sh.getRange('X' + (lastRow - empty_rows.length + 1) + ':Y' + (lastRow - empty_rows.length + 5)), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);

}

what is the question? You don't know how to detect which column was edited inside an onEdit(e) script? it is easy, get it from e.range.getColumn() — Ricardo Aranguren
– Ricardo Aranguren, Commented Apr 21, 2022 at 12:28
No, just want to add onOpen(e) to this script and also onEdit(e) if possible. — Mozart75
– Mozart75, Commented Apr 21, 2022 at 12:39
The difficulty is to detect also the event of multicells deleting at the same time! — Mike Steelson
– Mike Steelson, Commented Apr 21, 2022 at 12:40

Mike Steelson · Accepted Answer · 2022-04-21 12:58:27Z

1

Try this

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  if (sh.getName() != 'Suivi Clients') return;
  var editRange = { 
    top: 6, 
    left: 3, 
    right: 7 
  };
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;
  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;
  removeEmpty()
}

and

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 remove empty lines', 'removeEmpty')
    .addToUi();
  removeEmpty()
}

edited Apr 21, 2022 at 12:58

answered Apr 21, 2022 at 12:39

Mike Steelson

15.4k2 gold badges7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mozart75 Over a year ago

Thank you Mike, All is working nice!

Emel · Accepted Answer · 2022-04-21 12:40:23Z

0

I think your goal is as follows:

function onEdit(e) {
  const {range} = e
  /* Ensure that the edit occurs in the column 3 === C */
  if(range.getColumn() === 3 && range.getLastColumn() === 3){
    removeEmpty()
  }
}

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu().addItem('Remove Empty', 'removeEmpty').addToUi()
}

answered Apr 21, 2022 at 12:40

Emel

2,5041 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

Google sheets script Auto-run a script and add function in the menu bar

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related