I have a script, which amongst other things removes any formatting from my sheets. Works perfect, but I need to make a small change.

Instead of removing formatting from the entire sheet, I would just like to remove formatting from columns B - Y (all rows).

My code is below, does anyone know how I could tweak it?

Thanks guys

   function pricesearchsheet2() {

    var date = new Date();
    var currenthour = date.getHours();
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var cellcolour = "RED";
    var celltoleft = 0;
    var pastprice = 0;
    var currentprice = 0;
    var sending = "n";
    var tradingemailsfield = "C4";
    var playername = "null";
    var playernamecell = "A3";

  var targetcell = 1

// set the target cell based on the current hour of the day
  if (currenthour == 0) {
  var targetcell = "B6";
  var celltoleft = "A6";
}
if (currenthour == 1) {
  var targetcell = "C6";
  var celltoleft = "B6";
}
if (currenthour == 2) {
  var targetcell = "D6";
  var celltoleft = "C6";
}
if (currenthour == 3) {
  var targetcell = "E6";
  var celltoleft = "D6";
}
if (currenthour == 4) {
  var targetcell = "F6";
  var celltoleft = "E6";
}
if (currenthour == 5) {
  var targetcell = "G6";
  var celltoleft = "F6";
}
if (currenthour == 6) {
  var targetcell = "H6";
  var celltoleft = "G6";
}
if (currenthour == 7) {
  var targetcell = "I6";
  var celltoleft = "H6";
}
if (currenthour == 8) {
  var targetcell = "J6";
  var celltoleft = "I6";
}
if (currenthour == 9) {
  var targetcell = "K6";
  var celltoleft = "J6";
}
if (currenthour == 10) {
  var targetcell = "L6";
  var celltoleft = "K6";
}
if (currenthour == 11) {
  var targetcell = "M6";
  var celltoleft = "L6";
}
if (currenthour == 12) {
  var targetcell = "N6";
  var celltoleft = "M6";
}
if (currenthour == 13) {
  var targetcell = "O6";
  var celltoleft = "N6";
}
if (currenthour == 14) {
  var targetcell = "P6";
  var celltoleft = "O6";
}
if (currenthour == 15) {
  var targetcell = "Q6";
  var celltoleft = "P6";
}
if (currenthour == 16) {
  var targetcell = "R6";
  var celltoleft = "Q6";
}
if (currenthour == 17) {
  var targetcell = "S6";
  var celltoleft = "R6";
}
if (currenthour == 18) {
  var targetcell = "T6";
  var celltoleft = "S6";
}
if (currenthour == 19) {
  var targetcell = "U6";
  var celltoleft = "T6";
}
if (currenthour == 20) {
  var targetcell = "V6";
  var celltoleft = "U6";
}
if (currenthour == 21) {
  var targetcell = "W6";
  var celltoleft = "V6";
}
if (currenthour == 22) {
  var targetcell = "X6";
  var celltoleft = "W6";
}
if (currenthour == 23) {
  var targetcell = "Y6";
  var celltoleft = "X6";
}
if (currenthour == 24) {
  var targetcell = "Z6";
  var celltoleft = "Y6";
}
    for (var sheet of sheets) {
      sheet.getRange(targetcell).setValue('=--SPLIT(REGEXEXTRACT(JOIN(",",IMPORTDATA(B1)),"LCPrice"":""(.*)"), CHAR(34))');
      SpreadsheetApp.flush();
      var range = sheet.getDataRange();


     
      range.copyTo(range, { contentsOnly: true });
            
      //GET THE VALUE FROM THE CURRENT CELL AND THE PREVIOUS CELL, COMPARE THE VALUES IN ORDER TO WORK OUT IF PRICE HAS GONE UP OR DOWN.
      var pastprice = sheet.getRange(celltoleft).getValue();
      var currentprice = sheet.getRange(targetcell).getValue();

      if (currentprice == pastprice) {
        cellcolour = "orange"
      }
      else if (currentprice > pastprice) {
        cellcolour = "green"
      }
      else {
        cellcolour = "red"
      }
      sheet.getRange(targetcell).setBackground(cellcolour);
      //end of set colour functionality

// Send an email if the email request cell is set to Y
var tradingemailsfield = sheet.getRange('C4').getValue();
var playersname = sheet.getRange('A3').getValue();
if (tradingemailsfield == "y") {
MailApp.sendEmail("[email protected]", "Price Alert For " playersname, "The price of " playersname " has moved from " pastprice " to " currentprice " ");   
}
else{

}

    }


}

   

CodePudding user response：

Issue:

Instead of removing formatting from the entire sheet, I would just like to remove formatting from columns B - Y (all rows).

clearFormat:

You can remove format from a range easily with Range.clearFormat().

Here's an example of clearing format for columns B-Y in all sheets:

function clearFormat() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(sheet => {
    sheet.getRange("B:Y").clearFormat();
  });
}

copyTo:

Since clearFormat might not reset some formatting rules and it's not clear what formatting do you want to clear, you could also use Range.copyTo, as you are already doing, but with range B:Y instead of all sheet:

function copyTo() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(sheet => {
    const range = sheet.getRange("B:Y");
    range.copyTo(range, { contentsOnly: true });
  });
}