Google App Script, highlight new users since last month-CodePudding

I made a little function to retrieve the users we have on Google Workspace, and made a cron for this list to be refreshed once a month.

What I'd like to do now is to highlight cells of the new users created last month, but I didn't find a proper way to do it yet.

ie. next execution planned will be the 1st of June, I would need all accounts created in May to be highlighted

Any hint ?

My current code looks like this :

function listAllUsers() {

 var sh = '-- my sheet id --';
 var sheet = SpreadsheetApp.openById(sh);
 var sheet1 = sheet.getSheetByName('sheet title');
  
  var sheet1range = sheet.getRange("A:H")
  sheet1range.clear()
 
  var data = [];
  data.push(['Email' ,'First Name', 'Last Name',  'Suspended', 'Last Login Time','Creation', '2FA Active', 'Is admin']);
  var pageToken, page;
  do {
    page = AdminDirectory.Users.list({
    
      domain: '-- my domain --',
      pageToken: pageToken
    });
    var users = page.users;
    if (users) {
      for (var i = 0; i < users.length; i  ) {
        var user = users[i];
        data.push([user.primaryEmail, user.name.givenName, user.name.familyName, user.suspended, user.lastLoginTime , user.creationTime, user.isEnrolledIn2Sv, user.isAdmin ]);
      }
    } else {
    Logger.log('No users found.');
       }
    pageToken = page.nextPageToken;
  } while (pageToken);
  
  sheet1.getRange(1,1,data.length,data[0].length).setValues(data);
  var dated = sheet.getRange("P1")
  dated.setValue(Utilities.formatDate(new Date(),Session.getScriptTimeZone(),'dd-MMM-yyy'));
  
}

CodePudding user response：

I believe your goal is as follows.

Your Spreadsheet has the 1st header row and the data rows. The column "F" has the value of the created time of the user account.
You want to set the background color of the cells (columns "A" to "H") of the specific month for the column "F".
You want to achieve this using Google Apps Script.

Sample script:

function sample() {
  var checkYear = 2022; // Please set year.
  var checkMonth = 5; // Please set month. This sample is May.
  var color = "red"; // Please set the color you want to set.

  var sh = '-- my sheet id --';
  var sheet = SpreadsheetApp.openById(sh);
  var sheet1 = sheet.getSheetByName('sheet title');

  var range = sheet1.getRange("A2:H"   sheet1.getLastRow());
  var backgrounds = range.getValues().map(r => Array(r.length).fill(r[5].getFullYear() == checkYear && r[5].getMonth() == checkMonth - 1 ? color : null));
  range.setBackgrounds(backgrounds);
}

When this script is run, by checking the values of column "F", the background color of columns "A" to "H" is changed to "red" color.

When you want to include this script in your showing script, how about the following modification? In this case, please add the following script to the bottom of your function.

  var checkYear = 2022; // Please set year.
  var checkMonth = 5; // Please set month. This sample is May.
  var color = "red"; // Please set the color you want to set.

  var range = sheet1.getRange("A2:H"   sheet1.getLastRow());
  var backgrounds = range.getValues().map(r => Array(r.length).fill(r[5].getFullYear() == checkYear && r[5].getMonth() == checkMonth - 1 ? color : null));
  range.setBackgrounds(backgrounds);

References:

Added:

From your following reply,

Thanks for the hint ! You basically got the idea, but there's still need to do some manual settings in your example. As I want to run this script in a crontab and not care about it anymore, I would need to skip the checkYear and checkMonth config part. Is there any way to define these two vars automatically ?

If you want to set the background color of cells by this month, how about the following sample script?

Sample script:

function sample() {
  var color = "red"; // Please set the color you want to set.

  var date = new Date();
  var checkYear = date.getFullYear();
  var checkMonth = date.getMonth();

  var sh = '-- my sheet id --';
  var sheet = SpreadsheetApp.openById(sh);
  var sheet1 = sheet.getSheetByName('sheet title');
 
  var range = sheet1.getRange("A2:H"   sheet1.getLastRow());
  var backgrounds = range.getValues().map(r => {
    if (r[5]) {
      var d = new Date(r[5]);
      return Array(r.length).fill(d.getFullYear() == checkYear && d.getMonth() == checkMonth ? color : null)
    }
    return Array(r.length).fill(null);
  });
  range.setBackgrounds(backgrounds);
}