Googleスプレッドシートでできるいろんなこと
～リマインドBOTと自動生成スライドの作り方、おすすめアドオン～

=IFERROR(VLOOKUP(C2,BOT_config!$A$2:$B$8,2,FALSE),"")

const date = new Date();
const week = date.getDay();
const hour = date.getHours();
const minutes = date.getMinutes();
 
function remindBOT() {  
  const sheet = SpreadsheetApp.getActive().getSheetByName('BOT_remind');
  const rowLast = sheet.getLastRow()-2;
  const remind = sheet.getRange(3, 1, rowLast, 5).getValues();
  for(var i = 0; i < remind.length; i++){
    const re_hour = remind[i][0];
    const re_minutes = remind[i][1];
    const re_week = remind[i][4];
    const re_message = remind[i][3];
    const Blank = sheet.getRange(2, 5)
    if(hour == re_hour && minutes == re_minutes){
　　　　if(re_week == 7){
        messagePOST(re_message);
      }else if(week == re_week){
        messagePOST(re_message);
      }
      Logger.log('applicable');
    }else{
      Logger.log('not applicable');
    }
  }
}
 
function messagePOST(message) {
  const thdID = 00000000;
    const url = 'webhookのURL';//取得したwebhookのURLを入れる
    url += "&threadKey=" + thdID;
    const re_message = { 'text': message }
    const options = {
      'method': 'POST',
      'headers': {
        'Content-Type': 'application/json; charset=UTF-8'
      },
      'payload': JSON.stringify(re_message)
    };
    const result = UrlFetchApp.fetch(url, options);
    Logger.log(result);
}

function initSlide() {
  var url = 'スライドのURL';
  var slide = SlidesApp.openByUrl(url);
  var slide0 = slide.getSlides();
  for(var i = 2; i < slide0.length; i++){
    Logger.log("削除");
    slide0[i].remove();
  }
}
 
function generateSlidesFromSpreadsheet() {
  initSlide();
  var sheet = SpreadsheetApp.openByUrl('スプレッドシートのURL').getSheetByName('シート名');
  var slide = SlidesApp.openByUrl('スライドのURL');
 
  var datarange = sheet.getDataRange().getValues();
  var template = slide.getSlides()[1];
 
  for(var i=1;i<datarange.length;i++){
    var newpage = slide.appendSlide(template);
    for(var j=0;j<datarange[0].length;j++){
      newpage.replaceAllText('{'+datarange[0][j]+'}', datarange[i][j]);
    }
 
    var getimg = newpage.getImages();
 
    var image = datarange[i][3].replace('open?','uc?export=view&');
    Logger.log(image);
    try{
      getimg[0].replace(image);
    }catch(e){
      Logger.log("image false");
    }
  }
}