指定の日付範囲内のセールの背景色を変更したい

Question

前提・実現したいこと

EventInfoSheetの内容に応じて、カレンダー(FortestSheet)にイベント名、イベントタイプ、イベント期間のセールを特定の色に変更したいですが、実行しても何も起きなかったです。

【EventInfoSheet】
イベント詳細を格納するシート

【FortestSheet】
別の関数で3列目から1行目に月、2行目に日付(表示形式はdd)、3行目に曜日を出力させています。

該当のソースコード

function myFunction() {
  const FortestSheet = SpreadsheetApp.getActive().getSheetByName("fortest");
  const EventInfoSheet = SpreadsheetApp.getActive().getSheetByName("EventInfo");
  const EventInfoValues = EventInfoSheet.getDataRange().getValues();
  var LastCol = FortestSheet.getLastColumn()-2;
  var ValueList=[];
  
  for(var i=0;i<EventInfoValues.length;i++){
//タイトル行及びイベント名が空欄のデータを排除
  if(EventInfoValues[i][0]!="イベント名" && EventInfoValues[i][0]!=""){
  var TestList = [];
  TestList.push(EventInfoValues[i][0]);
  TestList.push(EventInfoValues[i][1]);
  TestList.push(EventInfoValues[i][2]);
  TestList.push(EventInfoValues[i][3]);
  ValueList.push(TestList);
  }
  }
for(var y=0;y<ValueList.length;y++){

var StartDate = ValueList[y][2];
var StartDate = Utilities.formatDate(StartDate, 'Asia/Tokyo', 'MM/dd'); 
var EndDate = ValueList[y][3];
var EndDate = Utilities.formatDate(EndDate, 'Asia/Tokyo', 'MM/dd'); 

for(var z=0;z<LastCol;z++){
//最終列までセールごとに日付判定
var CheckDate = FortestSheet.getRange(2,3+z).getValue();
//条件をCheckDate == StartDateに変更して見ましたが、それでも色は設定しなかったです
if(CheckDate >= StartDate && CheckDate<=EndDate){

FortestSheet.getRange(3,4+y).setBackground("blue");

}
}
}
}

FortestSheetに日付を一括出力する関数

  const SetCalenderByRealseDate=()=> {
    const TestSheet = SpreadsheetApp.getActive().getSheetByName("fortest");
    const realseDate = SpreadsheetApp.getActive().getSheetByName("Date").getRange("A2").getValue();
    var LastCol = TestSheet.getLastColumn()-2;
    var month=[];
    var SetDay=[];
    var SetDate=[];
    CountEndDate(realseDate,SetDay,SetDate,month,LastCol);
  
  for(var y = 0;y<SetDay.length;y++){
  TestSheet.getRange(1,3+y).setValue(month[y]);
   TestSheet.getRange(2,3+y).setValue(SetDate[y]); 
   TestSheet.getRange(3,3+y).setValue(SetDay[y]);
    }
    }
  
  const CountEndDate=(realseDate,SetDay,SetDate,month,LastCol)=>{
    const YobiList= ["日", "月", "火", "水", "木", "金", "土"];
    var StartDate = new Date(realseDate.setDate(realseDate.getDate()-1));
    for(var i=0;i<LastCol;i++){
    var CountDate = new Date(StartDate.setDate(StartDate.getDate()+1));
    var Yobi = CountDate.getDay();
    var monthDate = Utilities.formatDate(CountDate, 'Asia/Tokyo', 'MM'); 
//CheckDateは月の初めであるかを判定するため
    var CheckDate = Utilities.formatDate(CountDate, 'Asia/Tokyo', 'd'); 
    var CountDate = Utilities.formatDate(CountDate, 'Asia/Tokyo', 'MM/dd'); 
    var CountYobi = YobiList[Yobi];
    //一回目は月を配列に格納、それ以降は1日のみ月を格納し、他は空欄で出力
    if(i==0){
     month.push(monthDate);
    }
    else if(i!=0 && CheckDate==1){
    month.push(monthDate);
    }
    else { 
    month.push("");
    }
   SetDate.push(CountDate);
    SetDay.push(CountYobi);
    }
  }

//開始日や終了日のデータ形式につきまして、下記の関数の実行LOGを追記します
function myFunction() {
  const FortestSheet = SpreadsheetApp.getActive().getSheetByName("fortest");
  const EventInfoSheet = SpreadsheetApp.getActive().getSheetByName("EventInfo");
  const EventInfoValues = EventInfoSheet.getDataRange().getValues();
  var LastCol = FortestSheet.getLastColumn()-2;
  var ValueList=[];
  
  for(var i=0;i<EventInfoValues.length;i++){
  if(EventInfoValues[i][0]!="" && EventInfoValues[i][0]!=""){
  var TestList = [];
  TestList.push(EventInfoValues[i][0]);
  TestList.push(EventInfoValues[i][1]);
  TestList.push(EventInfoValues[i][2]);
  TestList.push(EventInfoValues[i][3]);
  ValueList.push(TestList);
  }
  }
  Logger.log(ValueList);
  }

/*Log ※if(EventInfoValues[i][0]!="" && EventInfoValues[i][0]!="")にすると、タイトル行が出力されてしまいます
[20-04-17 12:31:01:089 JST] [[イベント名, タイプ, 開始日, 完了日], [イベントA, イベントミッション, Sat Apr 04 00:00:00 GMT+09:00 2020, Fri Apr 24 00:00:00 GMT+09:00 2020], [イベントB, イベントステージ, Sun Apr 05 00:00:00 GMT+09:00 2020, Wed Apr 15 00:00:00 GMT+09:00 2020], [イベントC, イベントミッション, Mon Apr 06 00:00:00 GMT+09:00 2020, Wed Apr 08 00:00:00 GMT+09:00 2020], [イベントD, イベントステージ, Tue Apr 07 00:00:00 GMT+09:00 2020, Sat Apr 11 00:00:00 GMT+09:00 2020], [イベントE, イベントミッション, Fri Apr 10 00:00:00 GMT+09:00 2020, Tue Apr 14 00:00:00 GMT+09:00 2020]]
*/

Comments

[papinianus]

その図のどっちがどっちのシートかは明示したほうがいいと感じました。コード読んで処理読めば理解できるやろ、というのは不親切で回答者を遠ざける行為な気がします。

Answer 1

途中で投げ出してすみませんが、エラーの再現のためには提示のシートが必要となり、作るのが大変なのでイベント情報シートのみから新規に生成することにしました。そしたら色が塗れたのでデバッグする意味が私の中で消滅しました。
(下記のようなテーブル書式なら貼り付けできるのでデバッグ可能になります)

• EventInfoSheet

event	type	start	end	duration	detail
A	mission	2020/4/4	2020/4/24	20
B	stage	2020/4/5	2020/4/15	10
C	mission	2020/4/6	2020/4/8	2
D	stage	2020/4/7	2020/4/11	4
		2020/4/8	2020/4/12	4
		2020/4/9	2020/4/19	10
E	mission	2020/4/10	2020/4/14	4

この名前のシートにこういうデータがあれば q253621_re を実行するだけで新規シートに色のぬられたカレンダーができます。
実行時間はこのデータでだいたい 9 秒。あとは、データ量次第です。

const newSheet = name => SpreadsheetApp.getActive().insertSheet().setName(`${name} : ${Utilities.getUuid()}`); // 重複しないように uuid をつけているが、運用で対処するなら外していい
const formatSet = sheet => {
    sheet.setFrozenRows(3);
    sheet.setFrozenColumns(2);
    sheet.getRange(1, 1, 2, 2).merge().setHorizontalAlignment("center").setVerticalAlignment("middle").setBackground("black").setFontColor("white").setValue("イベントカレンダー");
    sheet.getRange(3, 1, 1, 2).setHorizontalAlignment("center").setVerticalAlignment("middle").setBackground("green").setFontColor("white").setValues([["イベント名", "タイプ"]]);
};
const dateToString = date => Utilities.formatDate(date, "Asia/Tokyo", "MM/dd");
const dayToString = num => ["日", "月", "火", "水", "木", "金", "土"][num];
const genDiffDays = d1 => d2 => (d2 - d1) / 86400000;
const generateCalendar = (start, end) => {
    let months = [];
    let dates = [];
    let days = [];
    let cur = new Date(start.getFullYear(), start.getMonth(), start.getDate());
    while (cur <= end) {
        months.push((cur.getMonth() + 1) + "");
        dates.push(cur);
        days.push(dayToString(cur.getDay()));
        cur = new Date(cur.getFullYear(), cur.getMonth(), cur.getDate() + 1);
    }
    return [months.map((e, i, a) => (i === 0 || e !== a[i - 1]) ? e : ""), dates, days];
};
const getRGBforEvent = (t) => {
    const define = {
        "mission": [255, 255, 0],
        "stage": [255, 0, 255]
    }
    return define[t] ? define[t] : [255, 255, 255]
}
const q253621_re = () => {
    const scheduleStart = new Date(2020, 3, 1); // スケジュールの開始日
    const scheduleEnd = new Date(2020, 5, 30); // スケジュールの終了日
    const eventsSheetName = "EventInfoSheet";
    // prepare
    if (scheduleEnd <= scheduleStart) return;
    const events = SpreadsheetApp.getActive().getSheetByName(eventsSheetName).getDataRange().getValues().slice(1).filter(([ev, tp, st, en]) => ev !== "" && tp !== "" && st <= en && en >= scheduleStart);
    const eventsCnt = events.length;
    if (eventsCnt < 1) return;
    // カレンダーを作る
    const target = newSheet(`${dateToString(scheduleStart)}-${dateToString(scheduleEnd)}`); // シートを作る
    /* シートを作りたくないなら↑を消して、こう。
    const target = SpreadsheetApp.getActive().getSheetByName("FortestSheet").clear();
    */
    formatSet(target);
    const calendar = generateCalendar(scheduleStart, scheduleEnd);
    target.getRange(1, 3, 3, calendar[0].length).setNumberFormat("dd").setValues(calendar);
    calendar[2].reduce((a,c,i)=>(c === "土" || c === "日")?a.concat(i):a,[]).forEach(e=>target.getRange(2,e+3,eventsCnt+2,1).setBackgroundColor("gray"));
    // イベント埋める
    target.getRange(4, 1, eventsCnt, 2).setValues(events.map(([e, t]) => [e, t]));
    target.autoResizeColumns(1, calendar[0].length + 2); // 記入項目は埋まったので、ここで列幅を調整
    const diffFromStart = genDiffDays(scheduleStart);
    events.map(([, t, s, e], i) => getRGBforEvent(t).concat([i + 4, 3 + diffFromStart(Math.max(scheduleStart, s)), 1, diffFromStart(Math.min(scheduleEnd, e)) - diffFromStart(Math.max(scheduleStart, s)) + 1])).forEach(e => target.getRange(...e.slice(3)).setBackgroundRGB(...e.slice(0, 3)));
};

Comments

[TopingMado]

お忙しい中、ご教授いただき、誠にありがとうございます。
イベントシートの情報に応じて、新規にカレンダシートを作成する形でも問題ございません。

一つ疑問がございますが、下記の日付で実行すると、3月1日から5月30日ではなく4月1日から6月30日まで出力されます。
また、scheduleStart、scheduleEndを別の日付にするとカレンダシートが作成されないです。
const scheduleStart = new Date(2020, 3, 1); // スケジュールの開始日
const scheduleEnd = new Date(2020, 5, 30); // スケジュールの終了日

[papinianus]

jsのdateのmonthは0から11です。カレンダーの月より1つ少なくなります。

[TopingMado]

失礼致しました。日付を変更してもカレンダーを新しく作成することができました。
お忙しい中、新しくコードを作成し、丁寧にご教授いただき、誠にありがとうございます。
理想としては土日、祝日の列を灰色に塗る予定するのと、イベントタイプに応じて背景色を変更できるとありがたいです。
土日祝日につきましては、別コード作成してなんとかなると思います。
イベントタイプに応じて背景色を変更できるようにアレンジして見たいと思います。
ご教授頂いたコードをうまく解読できていませんが、最悪の場合手動で色を変えれば済ます。
長々となりましたが、この度も貴重な時間割いて回答してください、重ねてお礼申し上げます。

[papinianus]

イベントタイプに応じての色はどこに設定を持つのですか、コード上できめていいのでしょうか。それとも eventinfo シートに列を増やすのでしょうか?
(UX 的に思いつきそうなところとして、eventinfo シートの背景色によって、色をつける、というのも可能ですが、やや処理が重くなりそうです)
想定するカレンダーの期間(2カ月程度なのか 1 年くらいなのか)とeventinfo の想定される行数をご教示ください。
土日はまあ土と日のある列を特定すればできるんで、そんなに難しい話じゃないと思いますが、↑が共有されるならあわせて考えてみます。

[TopingMado]

いつも大変お世話になっております。
また、何度も追加要望してしまい、お手数おかけしております中、ご丁寧に返信・対応してくださり、誠にありがとうございます。
①イベントタイプの応じて塗る色につきましては可能であれば、コード上で指定し、仮想配列で{"イベントタイプ": "〇〇", "色(RGB)":231, 239, 154 }のような形にできるとありがたいです。
②想定のカレンダーの期間につきまして、年単位で1年～2年があれば十分だと思います。
しかし、カレンダー作成後にもイベント追加することが多々ありまして、また、以前に開催されたイベントはリセットではなく保存しなければならないので、期間を短くにして新しいカレンダーを作成し、手動で使用中のカレンダーに貼り付ければ調整できると思います。

[papinianus]

動作確認できてませんが、こういう感じかなというのを。

イベントを追加する、ということなんですが、イベントを削除しなければ期間をいじって作りなおすことで、より長い期間のカレンダーができると思うのですが、そうはできないのでしょうか。仮に何か手書きがあるとしても、むしろ作り直したカレンダーに既存の内容をコピペしたほうがうまくいきそうです。また、イベントが削除され得るかどうかは設計を左右する問題です。
（現在のコードは、短くしても動きますが、期間から外れたイベントは意図的に外しているので、行数がずれてしまい、考えておられるような調整はむずかしいというか大変手間がかかると思います。仕様を小出しにするとこういう齟齬が起きます）

この表示で2年も作ったらみづらくてしょうがない気もしますが、そもそもシートの列数の上限は確認してますか？私は確認してないです。

再度伺いますがイベントの数はどの程度なんでしょうか。例えば 2 年で 10件なのか、100件なのか、1000件なのか。色をぬるにあたってイベントの数、イベントタイプの数がそれぞれ実行時間に影響します。10件だと気にすることはないと思いますが、多くなったとき実行時間制限にかかる可能性が生じます。

[papinianus]

https://itfun.jp/2010/10/google.html
> 合計で利用出来るセルの数は40万
1つのシートあたり256列まで
1つのブックでは100シートまで作ることが出来るようです。
なお、このいずれかの上限に達するまで利用出来るようです。

256 列が最大なので 1 年分すら作れません。仕様を再検討ください。

[papinianus]

RGB と 土日対応(祝日対応したくなるのが人情ですよね…やればやるほどグーグルカレンダーでよいように思えてなりません)。ここまでは動作確認しました。

期間を指定したときに、その期間に含まれないイベントを削除する部分は維持。

[TopingMado]

いつも大変お世話になっております。
お忙しい中、ご丁寧に返信・対応してくださり、誠にありがとうございます！

>>イベントを追加する、ということなんですが、イベントを削除しなければ期間をいじって作りなおすことで、より長い期間のカレンダーができると思うのですが、そうはできないのでしょうか。

既存のイベントを再度開催すると同時に新しくイベントも開催される形が多いですね。
そのため、期間を指定し新しくカレンダーを作成し、古いカレンダーを貼り付けるのが一番かと思います。

>>再度伺いますがイベントの数はどの程度なんでしょうか。

イベント数につきましては、コンテンツリリース時の場合は10件行かないですが、次第に既存イベントに加え、新規のイベントも追加される形となります。イベントタイプは10件以内は必ず収まります。

>>RGB と 土日対応(祝日対応したくなるのが人情ですよね…やればやるほどグーグルカレンダーでよいように思えてなりません)。

確かにグーグルカレンダーでも対応できますが、普段、イベントカレンダーはGoogleのサービスを使用できない海外の方に共有する必要があり、エクセルまたはその国にあるエクセルに類似したサービスで共有しています。そのため、エクセル関数よりGASで実行した方が、別のサービスに貼り付けても問題は発生しないため、GASで自動化を捗ろとした次第です。

いつも貴重な時間を割いて、丁寧に対応してくださっていること、重ねてお礼を申し上げます。

Answer 2

• if(EventInfoValues[i][0]!="イベント名" && EventInfoValues[i][0]!=""){ で空白ではなくイベント名であることを判定条件にしているので不適切
• if(CheckDate >= StartDate && CheckDate<=EndDate){ で checkdate は Date なのに StartDate や End Date は文字列なのでまともに火掻きできない

こうかなー、というのを。

const q253621 = () => {
    const FortestSheet = SpreadsheetApp.getActive().getSheetByName("fortest");
    const EventInfo = SpreadsheetApp.getActive().getSheetByName("EventInfo").getDataRange().getValues().slice(1);
    const LastCol = FortestSheet.getLastColumn() - 2;
    let ValueList = [];

    for(let i = 0; i < EventInfo.length; i++) {
//タイトル行及びイベント名が空欄のデータを排除
        if(EventInfo[i][0] === "" && EventInfo[i][0] === "" ){ continue; } //両方とも空欄だったらスキップしたい ? (片方空欄は許す ?)
        //if(EventInfo[i][0] === "" || EventInfo[i][0] === "" ){ continue; } //片方でも空欄があったら許さない ?
        ValueList.push(EventInfo[i].slice(0,4));
    }
    for(let y = 0; y < ValueList.length; y++) {
        const StartDate = Utilities.formatDate(ValueList[y][2], 'Asia/Tokyo', 'MM/dd');
        const EndDate = Utilities.formatDate(ValueList[y][3], 'Asia/Tokyo', 'MM/dd');

        for(let z = 0; z < LastCol; z++) {
//最終列までセールごとに日付判定
            const CheckDate = Utilities.formatDate(FortestSheet.getRange(2, 3 + z).getValue(), 'Asia/Tokyo', 'MM/dd');
//条件をCheckDate == StartDateに変更して見ましたが、それでも色は設定しなかったです
            if(CheckDate >= StartDate && CheckDate <= EndDate){
                FortestSheet.getRange(3 + y, 3 + z).setBackground("blue");
            }
        }
    }
}

しかしこれ Date で比較していないのはまだよいとして、y * z で getValue してますけど、現実的な時間で終わるんですかね。表のサイズにもよるとは思いますが。
あと、 y で塗る行を求めるようですが、それだと2つのシートでイベント名とタイプの出現順が(空白行を除いて)対応していないといけないので、これでホントにいいのかなーという気がします。

Comments

[TopingMado]

説明に不足があり、不親切であること、申し訳ございません。
また、ご回答頂き、誠にありがとうございます。
ご教授いただきましたコードを試しに実行して見ましたが、下記のエラーが出ています。

const EventInfo = SpreadsheetApp.getActive().getSheetByName("EventInfo").getValues();(行 3)
TypeError: SpreadsheetApp.getActive(...).getSheetByName(...).getValues is not a function（行 3)

行3の.getSheetByName(...)に.getDataRange().を追加したところ、下記のエラーが出ます。

Exception: パラメータ（String,String,String）が Utilities.formatDate のメソッドのシグネチャと一致しません。（行 14）

・if(EventInfoValues[i][0]!="イベント名" && EventInfoValues[i][0]!="")で空白ではなくイベント名であることを判定条件にしているので不適切
こちらはタイトル行にイベント名を載せているためですが、getValuesの際に2行目からデータを獲得すれば空欄のみ判定しても大丈夫と思います。

・if(CheckDate >= StartDate && CheckDate<=EndDate){ で checkdate は Date なのに StartDate や End Date は文字列なのでまともに火掻きできない
CheckDateについては、別の関数で一括に日付を出力しており、書式は同じくMM/ddにしていますが、これだと比較できないでしょうか。

・空白表及びタイトル行を除いたあと、yでイベント名、イベントタイプを出力後、zで横方向で日付を比較して背景色を塗るイメージでした

[papinianus]

> 行3の.getSheetByName(...)に.getDataRange().を追加したところ
ミスでした。失礼しました
> Exception: パラメータ（String,String,String）が Utilities.formatDate のメソッドのシグネチャと一致しません。（行 14）
これがこうなるということは、EventInfoSheet の開始日や終了日は文字列なんですかね？日付ではないのですか？

> ・if(EventInfoValues[i][0]!="イベント名" && EventInfoValues[i][0]!="")
↑こうじゃなくて↓こう、ではないかという意味です。
> ・if(EventInfoValues[i][0]!="" && EventInfoValues[i][0]!="")

> CheckDateについては、別の関数で一括に日付を出力しており、書式は同じくMM/ddにしています

文字列どうしなら比較できると思います。コードをみても文字列です。ただ、fortestsheetの画像の左上の赤枠を見る限り、日付になっているように見えます。なぜかは分かりませんが。

> 空白表及びタイトル行を除いたあと、yでイベント名、イベントタイプを出力後、zで横方向で日付を比較して背景色を塗るイメージでした
誤解してました。fortestsheet にあらかじめイベントが埋まっているのだと思ってました。そうではないですね。からっぽのシートを埋めるということですね。だとすればその処理方法で支障はないと思います。大変失礼しました。

[TopingMado]

お忙しい中、ご返信いただき誠にありがとうございます。
ご質問につきまして、ご回答させていただきます。

>> Exception: パラメータ（String,String,String）が Utilities.formatDate のメソッドのシグネチャと一致しません。（行 14）

>これがこうなるということは、EventInfoSheet の開始日や終了日は文字列なんですかね？日付ではないのですか？

日付だと思います、念の為、EventInfoSheetから取得したデータの配列をLOGに出力し、追記させていただきました。

>> CheckDateについては、別の関数で一括に日付を出力しており、書式は同じくMM/ddにしています

>文字列どうしなら比較できると思います。コードをみても文字列です。ただ、fortestsheetの画像の左上の赤枠を見る限り、日付になっているように見えます。なぜかは分かりませんが。

元々はddだけ出力していましたが、そうなると月が欠けてしまうので、データを比較するためにMM/ddで出力するよう変更しました。しかし、データ表示の際はddだけ表示したく、dd形式で表示すると値が日付になってしまいました。

>> 空白表及びタイトル行を除いたあと、yでイベント名、イベントタイプを出力後、zで横方向で日付を比較して背景色を塗るイメージでした

>誤解してました。fortestsheet にあらかじめイベントが埋まっているのだと思ってました。そうではないですね。からっぽのシートを埋めるということですね。だとすればその処理方法で支障はないと思います。大変失礼しました。

こちらこそ説明不足で大変申し訳ございません。
fortestsheetにeventinfoのイベント名、イベントタイプを出力すると同時に、対応するイベント期間を色塗りするイメージとなります。

[papinianus]

「実行の準備をしています」となりスクリプトが実行できなくなりましたので確認できていません。

当方のミスで getDataRange を書き加えていただいていますが、これだと確認なさったように イベント名、イベントタイプといった文字のあるヘッダ行が取得されます。getRange(2,1,10,6) のようにするか、今修正したように getDataRange したあと 1 行目を捨てればいいと思われます。