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');
}
}