Google Sheets: Apps Script: Is there a way to automatically highlights and adjust the width of a col-CodePudding

I have a project roadmap tracker where each of the columns corresponds to the first date of the week. I have it automatically highlight the column if that date matches any date of the current week but the columns are so narrow that it's not easily legible. I want to expand the column that contains the a date that matches the current week when the sheet is opened.

I am not familiar with Google Apps script and have no idea how to approach this problem. Plus, hobbling together other example scripts have proven fruitless since I hardly know what these functions do with each other.

For highlighting the column of the current date/week, I'm using this conditional formatting formula that I would ideally prefer to be integrated into a script.

=AND(WEEKNUM(TODAY(),2)=WEEKNUM($2:$2,2),YEAR(TODAY())=YEAR($2:$2))

Where the formula looks for the date of each column in row 2 and checks if the date matches any date within this current week.

In short, I trying to write a script that:

On open
The script looks for the column that matches a date that exists within the current week
Automatically adjusts the column to be a specific width (like 200)
Automatically highlight that column a specific color (like pale red)

CodePudding user response：

Basically it could be something like this:

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  // get the dates from the first row of the sheet
  var dates = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues().flat();
  
  // get current week and year
  var today = new Date();
  var current_week = Utilities.formatDate(today, ss.getSpreadsheetTimeZone(), 'w');
  var current_year = today.getFullYear();

  // get indexes of the columns with dates belong current week and year
  var columns = [];
  for (let i in dates) {
    var date = new Date(dates[i]);
    var date_week = Utilities.formatDate(date, ss.getSpreadsheetTimeZone(), 'w');
    var date_year = date.getFullYear();
    if (date_year == current_year && date_week == current_week) columns.push( i   1);
  }

  // change width of the columns to 200
  columns.forEach(col => sheet.setColumnWidth(col, 200));

  // change backgrounds of the columns to pale red
  var last_row = sheet.getLastRow()
  columns.forEach(col => sheet.getRange(1,col,last_row,1).setBackground('#ffaaaa'));
}