Search code examples
if-statementgoogle-apps-scriptgoogle-sheetsvar

IF statement with variable in google Apps Script

I am trying to make a google spreadsheet document with some buttons in it for a few colleagues. The colleagues can indicate which silo they're working with by pressing a certain button. In this case, it is either 1, 2, 3 or 4. This value is then stored in a cell. Afterward, they can keep track of their production by pressing a plus or minus button every time they have produced a pallet of material. The amount of pallets produced is stored in a cell (in this case 'M4:M5'). Now I want to keep track of the number of pallets produced from each silo. So when they have selected silo 1, I want the number of pallets produced stored in a cell next to silo 1. When they select a different silo, I want to keep track of the pallets produced from that silo. I hope you kinda get what I am talking about.

I want to do this by using an If statement. So if silo 1 is selected, and they press the plus button because a pallet has been produced, I want a pallet to be added to a certain cell I made for silo 1. I tried to do this by storing the selected silo number as "temp" and then verifying it with an "if" statement. If it matches a certain silo number, the right cell will have a +1 added to it. The problem is that Apps Script is telling me that "temp" is unused in my code.

I am totally new to this coding language. Can someone please help me?

function PlusHVBB() {
var spreadsheet = SpreadsheetApp.getActive();
var tempHVBB = spreadsheet.getRange('M4:M5').getValue();
spreadsheet.getRange('M4:M5').setValue(tempHVBB+1);
var temp = spreadsheet.getRange('P4:P5').getValue();
if (temp = 1){

spreadsheet.getRange('C29').setValue(tempHVBB+1);
}

};

Solution

  • The problem is that you use getValue() on a range that contains more than one cell and that your comparison operator is incorrect

    • The problem with your if condition is that temp = 1 does not compare the value of temp against 1, but rather assigns it the value 1. To compare it against 1 you need to use the == or === operator: if (temp == 1)

    Furthermore:

    • 'M4:M5' is a range that contains two cells
    • You need to either get (and set!) the value of each single cell indvidually:
    var tempHVBB1 = spreadsheet.getRange('M4').getValue();
var tempHVBB2 = spreadsheet.getRange('M4').getValue();
    • Or you can use the methods getValues() and setValues() respectively to retrieve and set values from a range of cells. This will return you a nested array or values in the format [[row1/column1, row1/column2],[row2/column1, row2/column2]] or in your case: [['M4', 'M5']]