How to use for loop in the if statement?

user_2056451 0
Viewed 120

I'm try to develop progress bar chart for road construction using appscript. I have a tab for enter the form data. data entry form

And in this file have more tabs for multiple roads names. multiple bar chart for road names

It's like this bar chart interface

This is what I am trying to do. After entering the data in the data entry form, colorize the cell range of the specified location in the relevant barchart. This is where I first tried to find out where to start coloring. The following code was used for that.But running the "for loop" in the "if statement". But it does not happen successfully.

  var ss = SpreadsheetApp;
  var sheet=ss.getActiveSpreadsheet().getSheetByName("AddData");
  var actTabName = sheet.getRange("E1").getValue();
  var catName = sheet.getRange("B4").getValue();
  var start =sheet.getRange("B1").getValue();
  var end = sheet.getRange("B2").getValue();
  var side = sheet.getRange("B3").getValue();
  function setBarChart() {
  var markTab = ss.getActiveSpreadsheet().getSheetByName(actTabName);
  var lstRw = markTab.getLastRow();
  var lstCl = markTab.getLastColumn();

  Logger.log(lstRw);
  Logger.log(lstCl);

  var catList = markTab.getRange(1,3,lstRw-2,1).getValues();
  Logger.log(catList)

  for(var i=0; i<catList.length; i++){
  if(catList[i] == "Chainage"){
  var chRNo = i+1;
  
  var chList = markTab.getRange(chRNo,4,1,lstCl-3).getValues();     

   }
  }
  Logger.log("chRNo "+chRNo);  

 if(side == "LHS"){
 for(var j=0; j<8; j++){
  if(catList[i] == catName){
    var catLhRNo = j+1;
    Logger.log("catLhRNo = "+catLhRNo);
  
    }
    else{
    Logger.log("test1");
    }
    }
    }
    if(side == "RHS"){
    for (var k=8; k<11; k++){
    if(catList[0][k] == catName){
    var catRhRno = k+1;
    Logger.log("catRhRno = "+catRhRno);
    
    
    
    }else{
    Logger.log("test2");
    Logger.log(catList[7]);
    }
    }
    }  
   }
google-apps-script google-sheets
2 Answers

I'd advice to break complicated operations into small simple functions:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('AddData');

  // get the data from the sheet 'AddData'
  var [start, end, side, item] = sheet.getRange('b1:b4').getValues().flat();
  var tab = ss.getSheetByName(sheet.getRange('e1').getValue());

  // get the row index from given sheet (tab), side and item
  var row = get_row(tab, side, item);

  // colorize the row on given sheet
  colorize_row(tab, row, start, end);
}

// get the row index
function get_row(tab, side, item) {
  var items = tab.getRange('b1:b11').getValues().flat();
  if (side == 'LHS') return items.indexOf(item) + 1;
  if (side == 'RHS') return items.lastIndexOf(item) + 1;
}

// colorize cells on the given tab and row
function colorize_row(tab, row, start, end) {
  var chainage = tab.getRange(6, 2, 1, tab.getLastColumn()).getValues().flat();
  var start_index = chainage.indexOf(start) + 2;
  var end_index = chainage.indexOf(end) - start_index + 3;
  var color = tab.getRange('b' + row).getBackground();               // upd
  tab.getRange(row, start_index, 1, end_index).setBackground(color); // upd
}

Update

If you want to get row-index of 'Chainage' dynamically from B column you can change the function colorize_row() this way:

function colorize_row(tab, row, start, end) {
  var chainage_row = tab.getRange('b:b').getValues().flat().indexOf('Chainage') + 1;
  var chainage = tab.getRange(chainage_row, 2, 1, tab.getLastColumn()).getValues().flat();
  var start_index = chainage.indexOf(start) + 2;
  var end_index = chainage.indexOf(end) - start_index + 3;
  var color = tab.getRange('b' + row).getBackground();
  tab.getRange(row, start_index, 1, end_index).setBackground(color).mergeAcross();
}
user_142654690

Try something like this:

function totalfunk() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("AddData");
  var catName = sh.getRange("B4").getValue();
  var side = sh.getRange("B3").getValue();
  var msh = ss.getSheetByName(sh.getRange("E1").getValue());
  var catList = msh.getRange(1, 3, msh.getLastRow() - 2, 1).getValues();
  const catList1 = catList.slice(0, 8);
  let i = catList.indexOf("Chainage")
  if (~i) {//much quicker than using loop
    var chRNo = i + 1;
    var chList = msh.getRange(chRNo, 4, 1, msh.getLastColumn() - 3).getValues();
  }
  let j = catList.indexOf(catName)''
  if (~j && side == LHS) {
    var catLhRNo = j + 1;
  }
  if (~j && side == RHS) {
    var catRhRno = j + 1;
  }
}

I would not expect this to work for you without some adjustments on your part

user_72150910
Related