input - I'm trying to make an inventory sheet that automatically updates using a barcode scanner on google sheets

Question

It seems like my code should work. I want to take a string of text from an input box, which works fine in this code, and compare it to the text in a cell on my google sheet. It's part of a function that scans the column for a specific inventory item.

The variable textInput always gets populated correctly. That comes from the input box. But, the variable itemName, which is supposed to get populated from the google sheet, won't take the string from the sheet.

I have a sheet full of inventory items in column 2 and their quantities in column 1 and I am trying to make this code accept the name of something in an input box and then automatically scan for the item and update the amount in the inventory sheet.

Why can't I get the variable itemName to be populated by the text from a cell on the google sheet?

//'onOpen' makes this program begin when you open the spreadsheet that is refferenced
function onOpen(){

  //This variable lets you stop the dialogue box from reopening or allow it to keep reopening when you enter another item.
  var onOff="On";

  //begin a loop which will reopen 
  while(onOff=="On"){
   
    //Open the program which allows you to call information from a spreadsheet 
    var app=SpreadsheetApp;
  
    //Call the active spreadsheet and then the specific sheet called 'Inventory'
    var activeSheet=app.getActiveSpreadsheet().getSheetByName("Inventory");

    //Access the user interface
    var ui=app.getUi();

    //Initiate a prompt box
    var prompt=ui.prompt("Inverntory", "Please scan the QR code of the item you want to update", ui.ButtonSet.OK);

    //Extract the text logged from the input box
    var textInput=prompt.getResponseText();

    //Extract the button value from the input box ('OK' or 'X')
    var buttonButton=prompt.getSelectedButton();

    //Stop the dialogue box from reopening of 'X' iclicked on the dialogue box
    if(buttonButton=ui.Button.CLOSE){
      onOff="Off";
    }
    
    //Set the variable for scanning rows at row 2
    var cellRow=2;

    //Set the variable for scanning columns at column 2
    var cellColumn=2;

    //This variable will be populated with whatever cell is currently being scanned and compared to the text from the input box
    var itemName="unassigned";

    //This variable will count the cells that have been scanned and compared
    var counter=0;

    //This variable will be used to limit how many time the while loop can cycle
    var counterMax=500;    

    //This variable will be used to call and change the quantity of an inventory item
    var itemCount="unassigned";

    while(counter<counterMax){

      //This should get the text from the current cell being scanned for comparison    
      itemName=activeSheet.getRange(cellRow, cellColumn).getRichTextValue();
      
      //If the current cell matches the input box text, this if statement will executed
      if (itemName==textInput){
        
        //This should move the current cell being looked at to the left one, putting us in the quantity row
        cellColumn=cellColumn-1;

        //This should pull the value from the current cell
        itemCount=activeSheet.getRange(cellRow, cellColumn);

        //This should add 1 to the inventory value
        itemCount=itemCount+1;

        //This should update the inventory quantity to it's new value in the sheet
        activeSheet.getRange(cellRow, cellColumn).setValue(itemCount);

        //This should move the current column to the right one, putting us back in the column where the inventory items names are stored
        cellColumn=cellColumn+1;

        //This will set the counter to the limmit which should end the while loop
        counter=counterMax;
      } 
      else{

        //This should move the scanner to the next row
        cellRow=cellRow+1;

        //This should make the while loop one cycle closer to the max number of cycles
        counter=counter+1;
      }
    }
  }
}

Sorry, I know it's kind of long. But it's 50% comment. I'm just trying to have clean and easily understandable code.

question from:https://stackoverflow.com/questions/65906181/im-trying-to-make-an-inventory-sheet-that-automatically-updates-using-a-barcode

Answer 1

Waitting for answers