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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…