Home > Mobile >  Google Apps Script not formatting rows in Google Sheets
Google Apps Script not formatting rows in Google Sheets

Time:12-23

I'm trying to use the following Google Apps Script to format the rows in a Google Sheets document if the value in the status column - column J is equal to the value "inactive" or "refunded". The script saves and runs with out any errors but it's not making the requested formatting. Any ideas what I am doing wrong?

function onOpen() {
  // Add a custom menu to the spreadsheet
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Status Check')
      .addItem('Format Inactive', 'formatInactiveOrRefunded')
      .addToUi();
}

function formatInactiveOrRefunded() {
  // Get the active spreadsheet and the active sheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();

  // Get the data range for the sheet
  var dataRange = sheet.getDataRange();

  // Get the values from the data range as a 2D array
  var values = dataRange.getValues();

  // Loop through the rows of the values array
  for (var i = 0; i < values.length; i  ) {
    // Get the value in column J (10th column) for the current row
    var value = values[i][10];

    // If the value is "Inactive" or "refunded"
    if (value == "Inactive" || value == "refunded") {
      // Get the range for the entire row
      var rowRange = sheet.getRange(i 1, 1, 1, sheet.getLastColumn());

      // Set the font color to red, italicize the text, and add a strikethrough
      var font = rowRange.getFont();
      font.setColor('#FF0000');
      font.setItalic(true);
      font.setStrikethrough(true);
      rowRange.setFont(font);
    }
  }
}




CodePudding user response:

I've fixed some minor issues in your script, which seems to be working.

The part I fixed is in the loop,

  // Loop through the rows of the values array
  for (var i = 0; i < values.length; i  ) {
    // Get the value in column J (10th column) for the current row
    var value = values[i][9]; //!!!should be index 9, not 10

    // If the value is "Inactive" or "refunded"
    if (value == "Inactive" || value == "refunded") {
      // Get the range for the entire row
      var rowRange = sheet.getRange(i 1, 1, 1, sheet.getLastColumn());

      // Set the font color to red, italicize the text, and add a strikethrough
      rowRange.setFontColor('#FF0000')
              .setFontLine('line-through')
              .setFontStyle('italic');
    }
  }

  • Related