get cell +1 based on cell Value?

Rookie_Js 0
Viewed 69

I have a container bound Script and a htmlService Sidebar.

The sheet contains 2 columns B, C.
B contains a ID, C contains a item name.
You enter the ID into the html input and submit, if theres a ID matching the one you enter the row gets deleted.
Now i want to use the confirm() method to confirm the deletion like so: "Are sure that you want to delete "+itemName+" form your Inventory"

My problem is that im struggling to get the to the ID adjacent Item name cell value.

JS

var adjacentName = ??????

function showDeleteElectronics() {
  const ui = SpreadsheetApp.getUi();
  var deleteElectronicsForm = HtmlService.createTemplateFromFile('DeleteElectronicsHTML')
  deleteElectronicsForm.itemName = adjacentName;
  var html = deleteElectronicsForm.evaluate();
  html.setTitle("Elektronik Entfernen")
  ui.showSidebar(html);
}


function deleteElectronics(formObject) {
  var electronicsID = formObject.electronicsLocalID 
  var SS = SpreadsheetApp.openById("1GSzlzj7nHPIUt-RIJfsPFobtnLbuoXedtJk1x11BdT0");
  var SHEET = SS.getSheetByName("ElektronikBestand");
  var RANGE = SHEET.getDataRange();
  var DELETE_VAL = electronicsID;
  var LOCAL_ID = 1;

  function deleteItem(){
    
    var rangeVals = RANGE.getValues();
    
    //Reverse the 'for' loop.
    for(var i = rangeVals.length-1; i >= 0; i--){
      if(rangeVals[i][LOCAL_ID] === DELETE_VAL){
        
        SHEET.deleteRow(i+1); 
      };
    };
  };

  deleteItem();
}

HTML

      <div class="sidebarwrapper">
    
          <div class="xbuttonwrapper">
              <button class="xbutton" onclick="google.script.host.close()">
                  <svg class="x" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 212.982 212.982" style="enable-background:new 0 0 212.982 212.982" xml:space="preserve"><path style="fill-rule:evenodd;clip-rule:evenodd" d="m131.804 106.491 75.936-75.936c6.99-6.99 6.99-18.323 0-25.312-6.99-6.99-18.322-6.99-25.312 0L106.491 81.18 30.554 5.242c-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 0 6.99-6.99 6.99-18.322 0-25.312l-75.936-75.936z"/><g/><g/><g/><g/><g/><g/><g/><g/><g/><g/><g/><g/><g/><g/><g/></svg>
              </button>
          </div>
    
          <div class="titlewrapper">
              <img class="ctlogotitle" src="https://i.imgur.com/d1VMjvs.png">
              <h1 class="title">Elektronik <br> Entfernen</h1>
          </div>
    
          <div class="divider"></div>
    
          <form class="inputformwrapper" id="removeElectronicsFrom">
              
              <div class="inputblockwrapper">
                  <div class="labelwrapper">
                      <label class="requiredlabel" for="electronicsLocalID">Lokale ID</label>
                  </div>
    
                  <input class="inputfield" 
                      type="text"
                      placeholder="PREF00000001..."
                      minlength="12"
                      maxlength="12"                
                      id="electronicsLocalID"
                      name="electronicsLocalID"                    
                      required>
              </div>
    
              <div class="confirmbuttonwrapper">
                  <input class="confirmbutton" 
                      type="submit" 
                      value="Entfernen"                
                      id="removeElectronics">
              </div>
    
          </form>
    
      </div>

      <script>
            document.querySelector("#removeElectronicsFrom").addEventListener("submit", function (e) {
                let confirmString = "Are you sure that you want to delete " + itemName + "?";
                e.preventDefault();
                if (confirm(confirmString)) {
                  google.script.run.deleteElectronics(this);
                  $('#removeElectronicsFrom').trigger("reset");
                } else {
                  $('#removeElectronicsFrom').trigger("reset");
                }
            });
      </script>
Column B Column C
ID itemName
-- --
MUCH00000001 Item1
MUCH00000002 Item2
MUCH00000003 Item3
MUCH00000004 Item4
MUCH00000005 Item5
MUCH00000006 Item6
MUCH00000007 Item7
MUCH00000008 Item8
MUCH00000009 Item9
MUCH00000010 Item10
google-sheets google-apps-script javascript
1 Answers

You have to call the function twice. First to get itemname and then to delete:

Server side:

function getItemName(formObject) {
  const electronicsID = formObject.electronicsLocalID;
  const SHEET = getSheet();
  const RANGE = SHEET.getDataRange();
  const DELETE_VAL = electronicsID;
  const ITEMNAMECOL = 2;
  const LOCAL_ID = 1;
  const rangeVals = RANGE.getValues();
  //Reverse the 'for' loop.
  for (var i = rangeVals.length - 1; i >= 0; i--) {
    if (rangeVals[i][LOCAL_ID] === DELETE_VAL) {
      return {
        i,
      //id: rangeVals[i][LOCAL_ID],
        itemName: rangeVals[i][ITEMNAMECOL],
      };
    }
  }
  return {i, itemName: "not found"};
}
function getSheet() {
  const SS = SpreadsheetApp.openById(
    '1GSzlzj7nHPIUt-RIJfsPFobtnLbuoXedtJk1x11BdT0'
  );
  const SHEET = SS.getSheetByName('ElektronikBestand');
  return SHEET;
}
function deleteElectronics(i) {
  getSheet().deleteRow(i + 1);
}

Client side:

document.querySelector("#removeElectronicsFrom").addEventListener("submit", function (e) {
  e.preventDefault();
  google.script.run.withSuccessHandler(({i, itemName}) => {
    const confirmString = "Are you sure that you want to delete " + itemName + "?";
    if (confirm(confirmString)) {
      google.script.run.deleteElectronics(i);
      $('#removeElectronicsFrom').trigger("reset");
    } else {
      $('#removeElectronicsFrom').trigger("reset");
    }
  }).getItemName(this)
});
TheMaster0
Related