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

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

It's like this

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]);
    }
    }
    }  
   }

CodePudding user response：

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);
  if (~catList.indexOf("Chainage")) {//much quicker than using loop
    var chRNo = i   1;
    var chList = msh.getRange(chRNo, 4, 1, msh.getLastColumn() - 3).getValues();
  }
  if (~catList1.indexOf(catName) && side == LHS) {
    var catLhRNo = j   1;
  }
  if (~catList1.indexOf(catName) && side == RHS) {
    var catRhRno = k   1;
  }
}

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

CodePudding user response：

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;
  tab.getRange(row, start_index, 1, end_index).setBackground('yellow');
}