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 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
© 2024 OneMinuteCode. All rights reserved.