0
215
VisualizaçõesGoogle Sheets App Script If Else not working
Test1 Sheet
Test2 Sheet
I want to compare News01 from Test1 A Column with Test2 A Column, and need to fetch the corresponding Test2 B column value
So the result should be Finance in Sheet1 B column
But If I use else, even when if statement is true it's going to else statement. If I delete else statement, then if statement is working.
I don't know why when if statement is true, it's going to else statement by default
function test() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var source_sheet = sheet.getSheetByName("Test1");
var target_sheet = sheet.getSheetByName("Test2");
var lastRow = source_sheet.getLastRow();
var inputs = source_sheet.getRange('A' + lastRow).getValues().flat();
var days = target_sheet.getRange('A1:A').getValues().flat();
var codes = target_sheet.getRange('B1:B').getValues().flat();
inputs.forEach(function(input, count){
for(var i = 0; i < days.length; i++){
if(days[i].trim() == input.trim()){
source_sheet.getRange('B' + (count + source_sheet.getLastRow())).setValue(codes[i]);
Logger.log(codes[i]);
break;
}
else{
Logger.log("News not found")
}
}
});
}
·
Juan Pablo Isaza
2 Respostas
Responde à pergunta0
You are logging News not found for each comparisons with all the rows of Test2, which is improper.
Instead, you should only log when the comparison is finished.
function test() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var source_sheet = sheet.getSheetByName("Test1");
var target_sheet = sheet.getSheetByName("Test2");
var lastRow = source_sheet.getLastRow();
var inputs = source_sheet.getRange('A' + lastRow).getValues().flat();
var days = target_sheet.getRange('A1:A').getValues().flat();
var codes = target_sheet.getRange('B1:B').getValues().flat();
inputs.forEach(function(input, count){
let found = false;
for(var i = 0; i < days.length; i++){
if(days[i].trim() == input.trim()){
source_sheet.getRange('B' + (count + source_sheet.getLastRow())).setValue(codes[i]);
Logger.log(codes[i]);
found = true;
break;
}
}
if (!found) { Logger.log("News not found"); }
});
}
Or simply,
function test() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var source_sheet = sheet.getSheetByName("Test1");
var target_sheet = sheet.getSheetByName("Test2");
var lastRow = source_sheet.getLastRow();
var inputs = source_sheet.getRange('A' + lastRow).getValues().flat();
var days = target_sheet.getRange('A1:A').getValues().flat();
var codes = target_sheet.getRange('B1:B').getValues().flat();
inputs.forEach(function(input, count){
for(var i = 0; i < days.length; i++){
if(days[i].trim() == input.trim()){
source_sheet.getRange('B' + (count + source_sheet.getLastRow())).setValue(codes[i]);
Logger.log(codes[i]);
return;
}
}
Logger.log("News not found");
});
}
·
Juan Pablo Isaza
Relatório
0
function test() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh1 = ss.getSheetByName("Sheet0");
var sh2 = ss.getSheetByName("Sheet1");
var lastRow = sh1.getLastRow();
var vs1 = sh1.getRange(1,1,sh1.getLastRow()).getValues().flat().map(e => e.trim());
var vs2 = sh2.getRange(1,1,sh2.getLastRow(),2).getValues();
vs2.forEach(r => {
if(~vs1.indexOf(r[0].trim())) {
Logger.log(r[1]);
}
});
}
·
Juan Pablo Isaza
Relatório
Responde à pergunta