CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Keto
Keto

Reputation: 31

Google Sheets run the script in all sheets

I am trying to implement the following function on Open sheet but I want to change the tab color on all the sheets. With this function, it allows me to change color only to single tab.

function getFirstEmptyRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Section A";
  var sheet = ss.getSheetByName(sheetname);
  var column = sheet.getRange('F:F');
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] != "" ) {
    ct++;
  }
  Logger.log("Row "+ ct);
  var ax=sheet.getRange(ct, 7).getValue();
     if(ax == ""){
        sheet.setTabColor("ff0000")
  } else {
     sheet.setTabColor(null)     
    }
}

Upvotes: 1

Views: 1200

Answers (2)

Yuri Khristich
Yuri Khristich

Reputation: 14537

Try this:

function onOpen() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets.forEach(s => s.setTabColor("ff0000"));
}

It hard to tell from the provided code, probably you need this:

function onOpen() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for (var sheet of sheets) {
    var data = sheet.getDataRange().getValues(); // get all data
    var col_G = data.map(x => x[6]);             // get column G
    var last_cell = col_G.pop();                 // get last cell of column F

    if (last_cell == '') sheet.setTabColor("ff0000");
  }
}

Or even shorter:

function onOpen() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  for (var sheet of sheets) {
    var data = sheet.getDataRange().getValues();
    if (data.pop()[6] == '') sheet.setTabColor("ff0000");
  }
}

It will make a tab red if in last row that of the table the cell in column G is empty.

If you have several sheets that you want to change you can filter them by names this way:

var names = ['Sheet1', 'Sheet2', 'Sheet3'];
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
sheets = sheets.filter(s => names.includes(s.getName()));

// the rest of a code

Upvotes: 1

TheWizEd
TheWizEd

Reputation: 8606

I'm assuming you want to change the tab color for all sheets if the value in column F of the last row of any sheet is blank. There are other values in that row. I've include onOPen, onEdit and onChange.

function onOpen(e) {
  // onOpen is a simple trigger and doesn't need to be installed
  // don't really use e in this scenario
  getFirstEmptyRow();
}

function onEdit(e) {
  // onEdit is a simple trigger and doesn't need to be installed
  var sheet = e.range.getSheet();
  if( e.range.getRow() === sheet.getDataRange().getLastRow() ) {  
    if( e.range.getColumn() === 6 ) {
      if( e.value === "" ) getFirstEmptyRow();
    }
  }
}

function onChange(e) {
  // onChange is an installed trigger
  // don't really use e in this scenario
  getFirstEmptyRow();
}

function getFirstEmptyRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var i=0;
  var sheet = null;
  var value = null;
  var color = null;
  for( i=0; i<sheets.length; i++ ) {
    sheet = sheets[i];
    value = sheet.getRange(sheet.getDataRange().getLastRow(),7).getValue();
    color = value === "" ? "ff0000" : null;
    sheet.setTabColor(color);
  }
}

Upvotes: 1

Related Questions