CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Ludmilla
Ludmilla

Reputation: 13

How to block inserting rows in a Google Sheets spreadsheet?

I created a spreadsheet in which each row contains registration data for different vendors.

These entries are made by filling in cells in a single reserved row (row 3). Through the script, I created the MENU that calls the "Insert" function. That function creates a new row in the sheet (before row 5) and pastes the registration data filled in the reserved row (3).

var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var vendors_sheet = ss.getSheetByName('vendors');

function onOpen() {
  ui.createMenu('MENU')
    .addItem('Insert', 'insert')
    .addToUi();
}

function insert() {
  vendors_sheet.insertRowBefore(5);
  vendors_sheet.getRange('A5:AF5').setValues(vendors_sheet.getRange('A3:AF3').getValues());
  vendors_sheet.getRange('A3:AF3').clearContent();
}

However, some spreadsheet users are manually inserting a new row in the sheet to enter the registration data.

Do not allow this action.

I would like a way to inhibit the insertion of new rows manually by the spreadsheet, but without blocking the editing of the cells. So that the only possible way is using the "Insert" option of the MENU. Does anyone know if this is possible? And how can I do this?

I hope I was clear in my explanation.

Upvotes: 0

Views: 1728

Answers (1)

Cooper
Cooper

Reputation: 64040

Preventing users from inserting rows in a given sheet

These two triggered functions work together to prevent users from inserting rows into Sheet2. The onSelectionChange() keeps feed range information into Properties Service and when onMyChange() occurs it deletes the row that was just added because it only runs on changeType == "INSERT_ROW"

function onMyChange(e) {
  //Logger.log(JSON.stringify(e));
  if (e.changeType == "INSERT_ROW") {
    let s = PropertiesService.getScriptProperties().getProperty("range");
    let sh = e.source.getRange(s).getSheet();
    let row = e.source.getRange(s).getRow();
    if (sh.getName() == "Sheet2") {
      sh.deleteRow(row);
    }
  }
}

function onSelectionChange(e) {
  //Logger.log(JSON.stringify(e));
  const sh = e.range.getSheet();
  let rg = `${sh.getName()}!${e.range.getA1Notation()}`;
  PropertiesService.getScriptProperties().setProperty("range", rg);
  //e.source.toast(rg);
}

Admittedly, It's kind of a kluge

You will required installable onChange event (actually they're all installable) and onSelectionChange trigger. This will not work for changes by other scripts, although it is interesting to note that other scripts can cause onSelectionChange to fire when they make selection changes with activate();

Upvotes: 1

Related Questions