Search code examples
google-sheetsgoogle-apps-scripttriggers

How to edit script to format text based on column value, then hide that column in results?

I'll try to keep it simple.. I usually write book!! Here is my sheet.

I need my existing script to recognize specific values in one column on one sheet and then bold and change text color on another sheet.

My script pulls data from the Data sheet and creates a list of events on the Rotationlist sheet. It already works the way I want it except for making some rows bold with a different text color.

I need the rows on the Data sheet that have 'I need a driver' in the Status column to be bolded and the text color to be red after they are imported into the Rotationlist sheet.

Here is an example: Desired end result

Here is my script:

//this creates the trip schedule to use during a rotation meeting. choose the dates and check the box. Script
//does everything else
function onEdit(e) {

  // Logger.log(JSON.stringify(e)) // DEBUG

  var editedValue=e.value
  var editedSheet = e.range.getSheet().getName()
  var editedRow = e.range.rowStart
  var editedCol = e.range.columnStart
  // Logger.log("DEBUG: edited value = "+editedValue+", edited sheet = "+editedSheet+", edited row = "+editedRow+", edited Column = "+editedCol)

  var sheet2Watch = "RotationList"
  // Logger.log("DEBUG: edited sheet = "+editedSheet+", sheet to watch = "+sheet2Watch+", edited value = "+editedValue)

  // test for sheet and edited value
  if (editedSheet != sheet2Watch || editedValue != "TRUE") {
    // no match, do nothing
    // Logger.log("DEBUG: no match, do nothing")
    return
  }
  // Logger.log("DEBUG: match = build trip schedule")
  createRotationSchedule()
}
function createRotationSchedule() {

  var tripSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("RotationList")
  
  // get the number of rows of data - include a test for no rows
  var numOfRowsData = +tripSheet.getLastRow()-3
  // Logger.log("DEBUG: number of rows of data = "+numOfRowsData)
  if (numOfRowsData <1 ){
    // no data, insert some blank rows
    tripSheet.insertRowsAfter(3, 10) 
  }else{
    // delete the rows
    tripSheet.deleteRows(4, numOfRowsData)
    //Logger.log("DEBUG: deleted the rows")
  }

  // get the user variables
  var startDate = tripSheet.getRange("A1").getValue()
  var endDate = tripSheet.getRange("B1").getValue()
  // Logger.log("DEBUG: Start date = "+startDate+"\nEnd date = "+endDate)
  // Logger.log("DEBUG: value of Start date = "+startDate.valueOf())

  // get the data
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data")
  // get the number of rows
  // can't use getDataRange because formula extend beyond the length of actual data
  // use Column A to get the actual row count
  var aVals = dataSheet.getRange("A2:A").getValues();
  var aLast = aVals.filter(String).length;
  // Logger.log("DEBUG: Column A: number of rows = "+aLast)
  var dataRange = dataSheet.getRange(2,1,aLast,9)
  // Logger.log("DEBUG: the data range = "+dataRange.getA1Notation())
  var data = dataRange.getValues()
  
  // convert the date column of the data into a 1d array for analysis
  var dates = data.map(function(e){return e[0]})
  //Logger.log(dates) // DEBUG
  //Logger.log(dates.length) // DEBUG

  // create a temporary Array to hold the data rows
  var tripArray = new Array
  // create an array for a blank row
  // maybe there are better ways to do this BUT this works.
  var blankRow = ["","","","","","","","",""]
  
  // loop through dates to find matches of the Start and End dates
  // note NOT testing formatted date BUT tesing "valueOf"
  for (var i=0;i<dates.length;i++){
    // does this date fit between the Start and End dates
    if ((dates[i].valueOf() >= startDate.valueOf()) && (dates[i].valueOf() <= endDate.valueOf())){
      // Logger.log("DEBUG: i:"+i+", row "+(i+1)+" is a match: "+dates[i])

      // test for the first row
      // Logger.log("DEBUG: Test for first row")
      if (i==0){
        // match for first row    
        // Logger.log("DEBUG: First Row: i="+i)
        // build data 
        // create header row
        var dateHeader = [dates[i],"","","","","","","",""]
        tripArray.push(blankRow)
        tripArray.push(dateHeader)

        // remove the timestamp and replace with a blank
        data[i].splice(0, 1, '')
        // Logger.log("DEBUG: data = "+data[i])
       
        // create/repeat data row for number of busses required
        // Logger.log("DEBUG: Repeat x = "+data[i][2])
        for (var b=0;b<data[i][2];b++ ){  
          // create data row       
          tripArray.push(data[i]) 
        }

        // jump to next row of data in loop
        continue
      }

      // test for data that is the same date as previous row
      // Logger.log("DEBUG: test for same date: i="+i+", this date = "+dates[i].valueOf()+", previous date = "+dates[+i-1].valueOf())
      if (i>0){
        if (dates[+i-1].valueOf() == dates[i].valueOf()){
        // data for the same date
        // Logger.log("DEBUG: same date: i="+i)

        // remove the timestamp and replace with a blank
        data[i].splice(0, 1, '')
        // Logger.log("DEBUG: data = "+data[i])

        // create/repeat data row for number of busses required
        // Logger.log("DEBUG: Repeat x = "+data[i][2])
          for (var b=0;b<data[i][2];b++ ){  
            // create data row       
            tripArray.push(data[i]) 
          }  
        
        // jump to next row of data in loop
        continue 
        }
      }

      // test for data that is a new date
      // Logger.log("DEBUG: test for a new date: i="+i+", this date = "+dates[i].valueOf()+", previous date = "+dates[+i-1].valueOf())
      if (i>0){
        
        // not the first row, and not a match for the previous row
        // so... start a new date row
        // Logger.log("DEBUG: new date: i="+i)

        // build data 
        // create header row
        var dateHeader = [dates[i],"","","","","","","",""]
        tripArray.push(blankRow)
        tripArray.push(dateHeader)

        // remove the timestamp and replace with a blank
        data[i].splice(0, 1, '')
        // Logger.log("DEBUG: data = "+data[i])

        // create/repeat data row for number of busses required
        // Logger.log("DEBUG: Repeat x = "+data[i][2])
        for (var b=0;b<data[i][2];b++ ){  
          // create data row       
          tripArray.push(data[i]) 
        }     
        continue
      }
    }else{
      // Logger.log("DEBUG: NOT IN DATE RANGE: i="+i+", and date = "+dates[i])
    }  
  }

  // insert a blank row at the bottom of the data
  tripArray.push(blankRow)

  // update tripArray into the spreadsheet
  tripSheet.getRange(4,1,tripArray.length,tripArray[0].length).setValues(tripArray).setFontWeight("normal").setBackground("");
  // Logger.log("DEBUG: updated RotationList")

  // format Column A of the sheet - bold, left aligned
  tripSheet.getRange(4,1,tripArray.length,1).setFontWeight("bold").setHorizontalAlignment("left")

  // merge the date rows
  var startRow = 4
  var lc = 9 
  // get the trip dates from Column A
  var tripDates = tripSheet.getRange(4,1,tripArray.length).getValues()
  Logger.log(tripDates)
  // loop through the values in Column A
  // if not a string, then merge the row
  for (var i=0;i<tripDates.length;i++){
    var row = +i+4
    Logger.log("i:"+i+", row = "+row+", value = "+tripDates[i][0]+", type of = "+typeof(tripDates[i][0]))
    if (typeof(tripDates[i][0]) != "string"){
      // merge the row 
      tripSheet.getRange(row,1,1,lc).setBackground("#d5d7e0").mergeAcross()
    }
  }
}
Solution

  • You want to get the Font colour for Trips where the Trip number is NOT blank and the driver name IS blank.

    Insert the following lines of code into createRotationSchedule

    BEFORE

     tripSheet.getRange(row,1,1,lc).setBackground("#d5d7e0").mergeAcross()
    }
  }
}

    AFTER

          tripSheet.getRange(row,1,1,lc).setBackground("#d5d7e0").mergeAcross()
    }
  }

  // use tripArray to set row background to RED if tripnumber but not Driver name
  var dataRows = tripArray.length
  for (var r =0;r<dataRows;r++){
    // Logger.log("r:"+r+", row# = "+(r+startRow)+", tripnumber = "+tripArray[r][1]+", Driver = "+tripArray[r][7])
    if (tripArray[r][1] !==  "" && tripArray[r][7] == "") { // if trip number is NOT empty cell AND driver IS empty
      tripSheet.getRange((r+startRow),2,1,6).setFontColor('red').setFontWeight("bold")
    }
  }
}

    DATA - BEFORE

    before

    DATA - AFTER

    after