CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Solana_Station
Solana_Station

Reputation: 321

How to get and set current week date (Sundays) with Google Apps Script

Goal: When running the script, I want the script to be able to get the week date (needs to be Sunday and not Monday) and set it in a cell. For example, today is March 20th, 2021 and the starting week that I've defined is Sunday, so I want to output 2021-03-14 to a particular cell.

Research: I've looked for previous questions on the internet, however I was only able to find instances such as getting the week number or week day and nothing really about week date. I'm sorry if this is out there already.

And I was only able to find how to get the current month (example: getMonth() + 1) or current year (example: getFullYear()). But not current week.

Question: How do we do this in Google Apps Script? Do we have to do complex calculations?

Upvotes: 0

Views: 3572

Answers (2)

Nikko J.
Nikko J.

Reputation: 5543

Inspired with Raynos` answer here:

Try this:

function getSunday() {
  var curr = new Date();
  var first = curr.getDate() - curr.getDay();
  var sunday = Utilities.formatDate(new Date(curr.setDate(first)), "UTC", "YYYY-MM-dd");
  var sh = SpreadsheetApp.getActiveSheet();
  sh.getRange("A1").setValue(sunday);
}

Output:

enter image description here

Upvotes: 2

Cooper
Cooper

Reputation: 64120

I have a simple function that puts a simple calendar on a spreadsheet. If you want to change the way you display the calendar just read the first comment.

function calendarMonthlyForYear() {
  var dA=['Sun','Mon','Tue','Wed','Thu','Fri','Sat'];
  var oA=['Mon','Tue','Wed','Thu','Fri','Sat','Sun'];//you can change the first day of the week by shifting the array
  //var oA=['Sun','Mon','Tue','Wed','Thu','Fri','Sat'];//you can change the first day of the week by shifting the array
  var dObj={};
  oA.forEach(function(e,i){dObj[e]=i});
  var mA=['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'];
  var cA=[];
  var bA=[];
  var wA=[null,null,null,null,null,null,null];
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Calendar");
  if(!sh){var sh=ss.insertSheet('Calendar');}
  sh.clear();
  var year=new Date().getFullYear();
  for(var i=0;i<12;i++) {
    var month=new Date(year,i,1).getMonth();
    var dates=new Date(year,i+1,0).getDate();
    cA.push([mA[month],dates,'','','','','']);
    bA.push(['#ffffff','#ffffff','#ffffff','#ffffff','#ffffff','#ffffff','#ffffff']);
    cA.push(oA)
    bA.push(['#ffff00','#ffff00','#ffff00','#ffff00','#ffff00','#ffff00','#ffff00']);
    var d=[];
    var ddd=[];
    for(var j=0;j<dates;j++) {
      var day=new Date(year,i,j+1).getDay();
      var date=new Date(year,i,j+1).getDate(); 
      if(day<wA.length) {
        wA[dObj[dA[day]]]=date;
      }
      //if(day==wA.length-1 || date==dates) {
      if(dA[day]==oA[wA.length-1] || date==dates) {
        cA.push(wA);
        bA.push(['#ffffff','#ffffff','#ffffff','#ffffff','#ffffff','#ffffff','#ffffff']);
        wA=['','','','','','',''];
      } 
    }  
  }
  sh.getRange(1,1,cA.length,cA[0].length).setValues(cA);
  //sh.getRange(1,1,cA.length,cA[0].length).setBackgrounds(bA);
  sh.getRange(1,1,cA.length,cA[0].length).setBackground('#ffffff');
}

function daysInMonth(m=0) {
  return new Date(new Date().getFullYear(),m+1,0).getDate();
}

I did it manually but you could do it script

Upvotes: 1

Related Questions