條件邊界問題排查（Google表格）

我有一個包含 5 行或 7 行塊的四種不同處理型別的資料集。我想在每個處理集之間添加邊框，所以我嘗試修改Tedinoz 的代碼。問題是這段代碼只為唯一值添加了一個邊框，我想在值與前一行不同時添加一個邊框。如何修改 if 陳述句if (Treatments.indexOf(row) == -1)以搜索“Treatment”的值與前一行不同的行？

這是完整的代碼：

function lineBetween() {

    //setup spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Sheet1");

    // get key variables
    var LastRow = 417
    var LastColumn = sheet.getLastColumn();
    var NumColumns = 8;

    // allow for headers
    var headerRows = 1;

    //erase any current formatting 
    var ClearRange = sheet.getRange(1, 1, LastRow, NumColumns).setBorder(false, false, false, false, false, false); // clear all formatting

    // get the data
    var data = sheet.getRange(( 1   headerRows), 1, (LastRow - headerRows), LastColumn).getValues();

    // setup new array  
    var Treatments = new Array();

    // Loop through treatments(Column C)
    for (var i in data) {

        var row = data[i][2].toString();
        // Logger.log("Inside LOOP: i = " i ", value = "  row);// DEBUG

        // search for unqiue values
        if (Treatments.indexOf(row) == -1) { // if value =-1, then the variable is unique

            // Logger.log("Inside IF#1: i = " i ", " row " is not referenced. Adding it");//DEBUG
            // underline the previous row

            if (i != 0) {
                // This IF statement to avoid underlining the Header row
                var range = sheet.getRange(( i   1   headerRows), 1, 1, NumColumns).setBorder(true, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID); // format if true
            }
            // continue to build array
            Treatments.push(row);
        }

    }
    // underline the last row of the treatments column
    var range = sheet.getRange(LastRow, 1, 1, NumColumns).setBorder(null, null, true, null, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true
    //Logger.log(Treatments);// DEBUG
}


  [1]: https://stackoverflow.com/questions/53053492/conditional-borders-in-google-sheets

uj5u.com熱心網友回復：

試試這個：

function lineBetween() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("Sheet1");
  sh.getDataRange().setBorder(false, false, false, false, false, false); // clear all formatting
  let sr = 2;//data start row
  const data = sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastColumn()).getValues();
  data.forEach((r, i, arr) => {
    if (i > 0) {
      if (r[2] != arr[i - 1][2]) {
        sh.getRange(i   sr, 1, 1, sh.getLastColumn()).setBorder(true, true, true, true, false, false);
      }
    }
  });
}

實際上，我認為這對我更有效：

function lineBetween() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("Sheet0");
  sh.getDataRange().setBorder(false, false, false, false, false, false); // clear all formatting
  let sr = 2;//data start row
  const data = sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastColumn()).getValues();
  let uA = [];
  let dA = [];
  data.forEach((r, i, arr) => {
    if (i > 0) {
      if (r[2] != arr[i - 1][2]) {
        sh.getRange(i   sr, 1, 1, sh.getLastColumn()).setBorder(true, false, false, false, false, false);
      }
    }
  });
}

標籤：

上一篇：GoogleAppsScript-從引數中的給定單元格獲取單元格偏移值

下一篇：appendRow()在谷歌表中添加空白行（應用程式腳本）