删除格式，但只针对特定列?

Question

我有一个脚本，其中包括从我的表格中删除任何格式。工作完美，但我需要做一个小的改变。

我想删除B - Y列(所有行)的格式，而不是从整个工作表中删除格式。

我的代码在下面，有人知道我如何调整它吗?

谢谢大家

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("[e-mail]", "Price Alert For "+playersname, "The price of "+playersname+" has moved from "+pastprice+" to "+currentprice+" ");   
}
else{
}
}

}

Answer 1

Issue:

我只想删除B - Y列(所有行)的格式，而不是从整个工作表中删除格式。

clearFormat:

可以删除格式从一系列容易Range.clearFormat()。

下面是清除所有工作表中列B-Y格式的示例:

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

copyTo:

由于clearFormat可能不会重置一些格式规则，并且不清楚您想要清除的格式，您也可以使用Range。copyTo，就像你已经做的那样，但范围是B:Y而不是所有的sheet:

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