Check if adjacent row is empty

Question

Current State:
I have a script which deletes a row based on a cell value,
bound to a sheet with 3 Columns: A = Global-ID, B = Local-ID, C = itemName.

The Problem:
I want to prevent Users from deleting rows with a Global-ID:
If removeItemFrom gets submitted
⟶ check if the adjacent cell in column A is empty.
⟶ if the adjacent cell in column A is not empty, return error.

JS

function showDeleteItem() {
  const ui = SpreadsheetApp.getUi();
  var html = HtmlService.createTemplateFromFile('DeleteItemHTML')
  .evaluate();
  html.setTitle("Delete Item")
  ui.showSidebar(html);
}


function getItemName(formObject) {
  const locItemID = formObject.itemLocalID;
  const SHEET = getItemSheet();
  const RANGE = SHEET.getDataRange();
  const DELETE_VAL = locItemID;
  const ITEMNAMECOL = 3;
  const LOCAL_ID = 2;
  const rangeVals = RANGE.getValues();
  for (var i = rangeVals.length - 1; i >= 0; i--) {
    if (rangeVals[i][LOCAL_ID] === DELETE_VAL) {
      return {
        i,
        itemName: rangeVals[i][ITEMNAMECOL],
      };
    }
  }
  return {i, itemName: "Item not found"};
}
function getItemSheet() {
  const SS = SpreadsheetApp.openById(
    '1GSzlzj7nHPIUt-RIJfsPFobtnLbuoXedtJk1x11BdT0'
  );
  const SHEET = SS.getSheetByName('Inventory');
  return SHEET;
}
function deleteItems(i) {
  getItemSheet().deleteRow(i + 1);
}

HTML

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Stylesheet'); ?>
    <?!= include('jQuery'); ?>
  </head>
  <body>
  <div class="sidebarwrapper">
      <div class="xbuttonwrapper">
          <button class="xbutton" onclick="google.script.host.close()">
              <svg class="x" enable-background="new 0 0 212.982 212.982" viewBox="0 0 212.98 212.98" xml:space="preserve" xmlns="http://www.w3.org/2000/svg"><path d="m131.8 106.49 75.936-75.936c6.99-6.99 6.99-18.323 0-25.312-6.99-6.99-18.322-6.99-25.312 0l-75.937 75.937-75.937-75.938c-6.99-6.99-18.322-6.99-25.312 0-6.989 6.99-6.989 18.323 0 25.312l75.937 75.936-75.937 75.937c-6.989 6.99-6.989 18.323 0 25.312 6.99 6.99 18.322 6.99 25.312 0l75.937-75.937 75.937 75.937c6.989 6.99 18.322 6.99 25.312 0s6.99-18.322 0-25.312l-75.936-75.936z" clip-rule="evenodd" fill-rule="evenodd"/></svg>
          </button>
      </div>
      <div class="titlewrapper">
          <img class="ctlogotitle" src="https://i.imgur.com/d1VMjvs.png">
          <h1 class="title">Artikel Entfernen</h1>
      </div>
      <div class="divider"></div>
      <form class="inputformwrapper" id="removeItemFrom">          
          <div class="inputblockwrapper">
              <div class="labelwrapper">
                  <label class="requiredlabel" for="itemLocalID">Lokale ID</label>
              </div>
              <input class="inputfield" 
                  type="text"
                  placeholder="PREF000001..."
                  minlength="10"
                  maxlength="10"                
                  id="itemLocalID"
                  name="itemLocalID"                    
                  required>
          </div>
          <div class="confirmbuttonwrapper">
              <input class="confirmbutton" 
                  type="submit" 
                  value="Entfernen"                
                  id="removeItem">
          </div>
      </form>
  </div>
  <script>
      document.querySelector("#removeItemFrom").addEventListener("submit", function (e) {
        e.preventDefault();
        google.script.run.withSuccessHandler(({i, itemName}) => {
          const confirmString = 'Are you sure you want to delete "' + itemName + '"?';
          if (confirm(confirmString)) {
            google.script.run.deleteItems(i);
            $('#removeItemFrom').trigger("reset");
          } else {
            $('#removeItemFrom').trigger("reset");
          }
        }).getItemName(this)
      });
  </script>
  </body>
</html>

Columns

Column A	Column B	Column C
Global-ID	Local-ID	itemName
--	--	--
000000000001	MUCH00000001	Item1
000000000002	MUCH00000002	Item2
000000000003	MUCH00000003	Item3
000000000004	MUCH00000004	Item4
000000000005	MUCH00000005	Item5
	MUCH00000006	Item6
	MUCH00000007	Item7
	MUCH00000008	Item8
000000000009	MUCH00000009	Item9
	MUCH00000010	Item10

It seems you don't understand arrays. [This](https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept) might help. — TheMaster, Sep 22 '22 at 19:37

score 0 · Answer 1 · answered Sep 22 '22 at 17:54

If I understand correctly, you are trying to prevent users from modifying only existing information from column A. If so, you can use the following function in your script:

function onEdit(e) {
  let ssRange = SpreadsheetApp.getActive().getActiveRange();
  let newVal = e.value;
  let oldVal = e.oldValue;
  let col = ssRange.getColumn();
  if(oldVal != newVal && oldVal != undefined && col == 1)
  {
    ssRange.setValue(oldVal);
  }
}

Example:

The script lets you add information when you have an empty cell in column A, however, if the cell has content on it and you try to modify it, it will automatically remove your edit and put the old value instead.

I used the onEdit trigger to detect any changes on the sheet and by getting the event object from the edit you can retrieve some information like the change type, the values that were changed, etc.

References:

Thanks for your answer, but it is not at all what I'm looking for. I've updated my question, it might be a bit easier to understand now. — Rookie_Js, Sep 22 '22 at 18:19

Check if adjacent row is empty

1 Answers1

Example:

References: