How to build conditional formattings with script?

0
votes

I'm using the google script to manage the google admin. Below is the script to list all google users in the domain.

If I create conditional formatting directly on the sheet, the rules are deleted whenever the script is launched. That's why I'd like to include the conditional formatting within the script itself, but it's not working?.

Basically what I want are :

  • if the value of the columns 'is Super Admin?' or 'is Delegated Admin?' are TRUE, then I'd like the rows to be colored in bold red.
  • And I'm trying to find out how to convert the results of lastLoginTime and creationTime (which are in string ISO8601 to datevalue, I know how to do it using formula, but I don't know how to using the script)

I'd really appreciate it if anyone can guide me.

Many many thanks in advance.

function listAllUser() {
  var sheet11 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetNAME");
  var sheet11range = sheet11.getRange("I3:Q")
  sheet11range.clear()
 
  var data = [];// array to store values
  data.push(['Last Name','First Name','Email','orgUnitPath', 'is Suspended?','is Super Admin?','is Delegated Admin?', 'LastLoginTime','Creation']);// store headers
  var pageToken, page;
  
  do {
    page = AdminDirectory.Users.list({    
      domain: 'DomainNAME',    
      pageToken: pageToken,
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i++) {  
        try{      
        var user = users[i];
        data.push([user.name.familyName,user.name.givenName,user.primaryEmail,user.orgUnitPath, user.suspended,user.isAdmin,user.isDelegatedAdmin, user.lastLoginTime , user.creationTime  ]);//store in an array of arrays (one for each row)}
    }catch (e){} 
       }} 
    pageToken = page.nextPageToken;
  } while (pageToken);
  sheet11.getRange(2,9,data.length,data[0].length).setValues(data).setVerticalAlignment("middle").setWrap(true);
  var t1 = sheet11.getRange("I1:P1").merge();
  var t1bis = t1.setValue("List of all USERS");
  var dated = sheet11.getRange("Q1")
  dated.setValue("Last updated: " + Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'dd-MM-yy hh:mm'));
  var range = sheet11.getRange("I1:Q2");
  range.setFontWeight("Bold").setFontColor("Blue").setHorizontalAlignment("center").setVerticalAlignment("middle").setBackground("#beedda").setWrap(true);


  var isSuperAdm = sheet11.getRange("N3:N").getValue();
  var isDelegatAdm = sheet11.getRange("O3:O").getValue();
  if(isSuperAdm==true || isDelegatAdm==true){
    range.setFontWeight('bold').setFontColor('red')
  } 
  
}
2
google-apps-scriptgoogle-sheets

2 Answers

0
votes

After looking around for some tutorials, I finally succeeded to have what I wanted, formating the cell :

  • when the lastLoginTime was before the specific date
  • when the value of 'is Super Admin?' and 'is Delegated Admin?' are true

function cellsFormatting(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName");
  var range1 = sheet.getRange("A1:Q2");
  range1.setFontWeight("Bold").setFontColor("Blue").setHorizontalAlignment("center").setVerticalAlignment("middle").setBackground("#beedda").setWrap(true);
  var range2 = sheet.getRange ("A3:Q");
  range2.setVerticalAlignment('middle').setWrap(true);
}

function setRangeToRedBoldFont(cellRange){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(cellRange);
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo("TRUE")
    .setFontColor("#FF0000")
    .setBold(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}

function setRangeToGrayItalicFont(cellRange){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(cellRange);
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied("=DATEVALUE(MID($P3;1;10)) + TIMEVALUE(MID($P3;12;8))<date(2018;8;31)")
    .setFontColor("#A9A9A9")
    .setItalic(true)
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}

function listAllUser() {
  var sheet11 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName");
  var sheet11range = sheet11.getRange("I3:Q")
  sheet11range.clear();
  cellsFormatting();
  
  var t1 = sheet11.getRange("I1:P1").merge();
  var t1bis = t1.setValue("List of all USERS");
  var dated = sheet11.getRange("Q1")
  dated.setValue("Last updated: " + Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'dd-MM-yy hh:mm'));  //will input the date & time when the script was launched

  var data = [];// array to store values
  data.push(['Last Name','First Name','Email','orgUnitPath', 'is Suspended?','is Super Admin?','is Delegated Admin?', 'LastLoginTime','Creation']);// store headers
  var pageToken, page;
  
  do {
    page = AdminDirectory.Users.list({    
      domain: 'DomainNAME',
      pageToken: pageToken,
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i++) {  
        try{      
        var user = users[i];
        data.push([user.name.familyName,user.name.givenName,user.primaryEmail,user.orgUnitPath, user.suspended,user.isAdmin,user.isDelegatedAdmin, user.lastLoginTime , user.creationTime  ]);//store in an array of arrays (one for each row)}
    }catch (e){} 
       }} 
    pageToken = page.nextPageToken;
  } while (pageToken); 
  sheet11.getRange(2,9,data.length,data[0].length).setValues(data);

  var formatRange = "I3:Q";
  setRangeToRedBoldFont(formatRange);
  setRangeToGrayItalicFont (formatRange);
}
0
votes

For the row color:

For user.isAdmin & user.isDelegatedAdmin

function colorLine() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('SheetName');
  const rg=sh.getRange(3,9,sh.getLastRow(),9);
  const vs=rg.getValues();
  vs.forEach((r,i)=>{
    if(r[5]||r[6]) {
      sh.getRange(i+3,1,1,sh.getLastColumn()).setFontWeight('bold').setFontColor('red');
    }
  });

}