如何修改Google Sheets脚本仅清除B-Y列格式而非整张工作表

修改说明 #

原代码清除全表格式的核心逻辑是：调用getDataRange()获取整张工作表的有数据区域，再通过仅粘贴内容的方式清除原有格式。仅需将这一步的目标范围调整为B到Y列的全部行即可。

核心修改点 #

将循环内的原有代码：

var range = sheet.getDataRange();
range.copyTo(range, { contentsOnly: true });

替换为：

// 获取工作表总共有多少行
var totalRow = sheet.getMaxRows();
// 定义范围：第1行开始，第2列（B列）开始，覆盖所有行，共24列（B到Y总共24列）
var range = sheet.getRange(1, 2, totalRow, 24);
range.copyTo(range, { contentsOnly: true });

修改后完整代码 #

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

    // 按当前小时设置目标单元格
    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();
        
        // 以下为修改后的清格式逻辑，仅清除B-Y列所有行格式
        var totalRow = sheet.getMaxRows();
        var range = sheet.getRange(1, 2, totalRow, 24);
        range.copyTo(range, { contentsOnly: true });
        
        // 对比价格设置单元格颜色
        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);
        
        // 满足条件发送邮件
        var tradingemailsfield = sheet.getRange('C4').getValue();
        var playersname = sheet.getRange('A3').getValue();
        if (tradingemailsfield == "y") {
            MailApp.sendEmail("my@mail.co.uk", "Price Alert For "+playersname, "The price of "+playersname+" has moved from "+pastprice+" to "+currentprice+" ");   
        }
    }
}

内容的提问来源于stack exchange，提问作者hamd01