0
320
ViewsHow to implement IF-ELSE statement to change the cell value using app script?
In here I am trying to change the cell value based on the IF-ELSE statement.
As you can see in the picture below, when user try to run the script for the first time, few files from google drive will be listed in the sheet with the STATUS mark as "new file" (row 2 till 5).
However, if there is any new files been uploaded into the google drive, user will run a script "check new files" to populate only the uploaded new files into the google sheet (row 6 and 7). But their STATUS mark is blank as shown in the picture.
Therefore i used the IF-ELSE statement, to convert the STATUS of row 2 till 5 (""new file" into "old file"") AND the blank row of 6 and 7 as "new file".
Here is the code:
function updateMarker(){
var sheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var cellValue = sheet.getRange("C2:C"+ sheet.getLastRow()).getValue();
Logger.log(cellValue);
if (cellValue == 'new file'){
sheet.getRange("C2:C"+ sheet.getLastRow()).setValue('OLD FILE');
}
if(cellValue == '' ){
sheet.getRange("C2:C"+ sheet.getLastRow()).setValue('NEW FILE');
}
}
If i run the code, it converts the whole cell value from row 2 till 7 as "old file". Which i guess it detects only the first if statement if (cellValue == 'new file').
My goal is
- to convert the "new file" marker into "old file"
- to set the empty cell value of the new uploaded files as "new file"
How can i solve this problem?
AFTER IMPLEMENTING THE SOLUTION CODE GIVEN
After i run for 2nd and 3rd time the STATUS changes vice versa.
Isn't the STATUS changes only if the cell value is "new file" and if there is empty cell ?
1 answers
Answer question0
Calling getValue() on a range of more than one cell will only return the value in the upper-left most cell in the range.
So the line
var cellValue = sheet.getRange("C2:C"+ sheet.getLastRow()).getValue()
actually just sets cellValue to the value in C2, or 'new file'.
This makes the line:
if (cellValue == 'new file')
always evaluate to true, so all cells are changed.
Modifications:
- Use
.getValues()instead ofgetValue() - Loop through all the cells
- Set the new
OLD FILE/NEW FILEarray back to the column range
function updateMarker() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
const cells = sheet.getRange("C2:C" + sheet.getLastRow())
const cellValues = cells.getValues()
Logger.log(cellValues)
const newValues = []
cellValues.forEach((row) => {
if (row[0] === "new file") {
newValues.push(['OLD FILE'])
}
else if (row[0] === "OLD FILE" || row[0] === "NEW FILE") {
newValues.push([row[0]])
}
else {
newValues.push(['NEW FILE'])
}
})
cells.setValues(newValues)
}