I want to change the format according to the day of the week.

This is my first time posting.
A spreadsheet is creating a calendar with the following format:

In line 3.4.5, we put together the functions so that changing the date of A4 changes.
In the third line, the number of weekdays = 0, Saturdays = 2, Sundays = 1, and holidays = 3 is used to determine holidays.

Under these conditions, when there is a change in the A4 cell, 1.3: gray color on the red text cell, 2: gray color on the blue text cell, and I am eager to use GAS to change the format.
Conditional formatting is not available because cells are also used for coloring.

I'm a complete beginner of GAS, and I tried to set it up while fishing for books and NETs, but I'm troubled because the format doesn't change even though it doesn't stop error.
I think I can still find the cause if I can check it step-in like ExcelVBA, but it ends without any response, so I don't know what's wrong with it...

Could someone please tell me how to correct it?
Thank you for your cooperation.

AB C DEF GH
3
  ----------------------------------------------------------------------------
4 January 2019 | January 1st | January 2nd | January 3rd | January 4th | January 5th | January 6th | January 7th | 
             -----------------------------------------------------------------
5 | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday | Monday | 
  ----------------------------------------------------------------------------
6 Ichiro Yamada | | | | | | | | 
  ----------------------------------------------------------------------------
7 Shinichi Yoshida | | | | | | | | |  
  ----------------------------------------------------------------------------
8 Kyoko Suzuki | | | | | | | | |  
  ----------------------------------------------------------------------------
9 Takashi Watanabe | | | | | | ----------------------------------------------------------------------------------------------

function myFunction2(){

    varsh=SpreadsheetApp.getActiveSpreadsheet();
    varshs = sh.getSheets(); // sheet specification

    for (vari=0;i<=11;i++) { // Repeat sheets 1-12

    /** Work on each sheet*/
      varrow=shs[i].getLastRow(); // Lowest row storage
      varcol=shs[i].getLastColumn();//LastColumn storage
      varrng = shs[i].getRange(4,1,row,col); // Range
      var Rng = shs[i].getRange(3,col); // line 3 stored

       while(Rng<=col){//Until last column
         varCr=Rng.getValue(); // reference line
         shs.setActiveRange(Rng); 

         if(shs.getRange(Cr).getValue(2)){
           shs.getActiveRangeList(rng).setBackground('#d9d9d9') // Cell Clay
           .setFontColor('#0000ff') // Saturday = 2 blue letters
         } else if(shs.getRange(Cr).getValue(1)){
           shs.getActiveRangeList(rng).setBackground('#d9d9d9') // Cell Clay
           .setFontColor('#ff0000') // Day = 1 Red Characters
         } else if(shs.getRange(Cr).getValue(3)){
           shs.getActiveRangeList(rng).setBackground('#d9d9d9') // Cell Clay
           .setFontColor ('#ff0000') // Celebration = 3 Red Characters
         } 
         else if(shs.getRange(Cr).getValue(0)){
           shs.getActiveRangeList(rng).(activeRangeList!==null) // No change
         }
       }
     }
   }

Dear PicoSushi, Thank you for your prompt reply.
Actually, I tried getDay first, but the start date didn't go ahead since 1899.
Is it possible to use the following code to reflect the date of A4?

function myFunction(){  
  var mySheet=SpreadsheetApp.getActiveSheet();//sheet specification
  varrng = mySheet.getRange(4,1).getValue(); // Reference year and year storage
  var myWeekDay=newArray("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"; // Day of Week
  var myDay = new Date(rng); // Specify year/month
  var myMonth=myDay.getMonth(); // Store Month

  myDay.setDate(2); // Start date is "1"
  Start with mySheet.getRange("B4").activate();//B4

  when(myMonth==myDay.getMonth()) {//myDay contains the year and year
    .getFullYear.getMonth()
    Enter the date in mySheet.getActiveCell().setValue(myDay.getDate()); //B4
    mySheet.getActiveCell().offset(10)//In the lower cell
    .setValue(myWeekDay[myDay.getDay()]); // Enter the day of the week
    mySheet.getActiveCell().offset(0,1).activate(); // Move cell to right
    myDay.setTime(myDay.getTime() + 1000*60*60*24);
  }
}

I wanted to do something about the previous operation from 1899/12/31 so I was frustrated in the middle of processing;;
If I can move while remembering, it will be 1900/1/1~ (waterfall sweat)

function myFunction(){  
  var mySheet=SpreadsheetApp.getActiveSheet();//sheet specification
  varrng = mySheet.getRange(4,1).getValue(); // Reference year and year storage
  var myWeekDay=newArray("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"; // Day of Week
  var myDay = new Date(rng); // Specify year/month
  var myMonth=myDay.getMonth(); // Store Month

  myDay.setDate(2); // Start date is "1"
  Start with mySheet.getRange("B4").activate();//B4

  while(myMonth==myDay.getMonth()) {//myDay contains the year and year.getFullYear.getMonth()
    Enter the date in mySheet.getActiveCell().setValue(myDay.getDate()); //B4
    mySheet.getActiveCell().offset(10)//In the lower cell
      .setValue(myWeekDay[myDay.getDay()]); // Enter the day of the week
    mySheet.getActiveCell().offset(0,1).activate(); // Move cell to right
    myDay.setTime(myDay.getTime() + 1000*60*60*24);
   }
}

google-apps-script

2022-09-30 21:36

1 Answers

Google Apps Script supports ECMAScript5 equivalent features, so you can use Date.prototype.getDay to retrieve the day of the week from the date.

var day=new Date('2018-11-07')
day.getDay();
// =>3 (0 equals Sunday, 1 equals Monday, and 3 equals Wednesday)

This allows you to map the corresponding background color corresponding to the day of the week and combine cell.setBackground.

Note: Date.prototype.getDay() - JavaScript|MDN

2022-09-30 21:36

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656