Convert to AppScript: Conditional Formatting based on Date and Time of Day-CodePudding

I am making a study planner for university in Gsheets. I read that in Gsheets' inbuilt Conditional Formatting, the cell border cannot be formatted. That's why I want to convert my cond. formatting to Apps Script, however this particular case is difficult to me as I only know very basic Apps Script.

The three Conditional Formatting rules are (applied to columns G&H, I&J and K&L respectively):

 =AND($D2=TODAY(); MOD(NOW();1)<TIME(12;0;0))

 =AND($D2=TODAY(); MOD(NOW();1)>TIME(12;0;0);MOD(NOW();1)<TIME(18;0;0))

 =AND($D2=TODAY(); MOD(NOW();1)>TIME(18;0;0))

Column D contains dates, and this rule colors cells G&H if it's before 12pm, colors columns I&J if it's between 12pm and 6pm, and colors columns K&L if it is past 6pm.

As I want to color the borders also, which is not possible without Apps Script, could someone help me to implement this is Apps Script?

CodePudding user response：

From what I understand the formulas you have created already return a TRUE and FALSE value, right?

Then it is very easy to apply a style to them depending on the value they contain. You simply have to go through the values, check if they contain TRUE or FALSE, and apply the styles depending on that condition.

Create a new bounded script in your Sheet via Extensions > Apps Script.
Copy this script:

function setBorderCells() {
  /* SELECT THE RANGE TO EVALUATE */
  const rangeOfDates = sS.getRange('G:L').getValues().filter(n => n[0] !== '')
  for (let i = 0; i < rangeOfDates.length;   i) {
    for (let k = 0; k < rangeOfDates[i].length; k  ) {
      /* AS THE VALUES STARTS IN THE G COLUMN WE START FROM (1,7) EQUAL TO G1 */
      const cell = sS.getRange(1   i, 7   k)
      if (cell.getValue()) {
        /* APPLY STYLES IF THE CONDITION IS TRUE */
        cell.setBorder(true, true, true, true, false, false, "green", SpreadsheetApp.BorderStyle.SOLID_THICK);
      } else {
        /* APPLY STYLES IF THE CONDITION IS FALSE */
        cell.setBorder(true, true, true, true, false, false, "red", SpreadsheetApp.BorderStyle.SOLID_THICK);
      }
    }
  }
}

From here, as @Ruben mentions in the comment, you will have to review the Apps Script documentation to apply the styles you want.

It should be clarified that this is not the most efficient way to do it, but I think it is a good introduction to how Google Apps Script works. For an optimization of this Script you should study how to use batch operation works and transform the script from there.

Documentation

CodePudding user response：

Try (adapt conditions, colors and limits of range as necessary)

function setBorderCells() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sh = ss.getActiveSheet()
  bordersUpdating(ss.getId(), sh.getSheetId(), 2, 6, 7, 8)
}
function bordersUpdating(id, gid, startRow, endRow, startColumn, endColumn) {
  const resource = {
    "requests": [
      {
        "updateBorders": {
          "range": {
            "sheetId": gid,
            "startRowIndex":  startRow - 1,
            "endRowIndex":  endRow,
            "startColumnIndex":  startColumn - 1,
            "endColumnIndex":  endColumn
          },
          "top": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "bottom": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "left": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "right": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "innerHorizontal": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
          "innerVertical": {
            "style": "SOLID",
            "width": 1,
            "color": {
              "blue": 1.0
            },
          },
        }
      }
    ]
  }
  Sheets.Spreadsheets.batchUpdate(resource, id);
}

Documentation

enable google sheets api servide