google-apps-script - 如何在Google脚本编辑器中的1到30个单元格中自动设置整个月的日期和日期（每个月31个）？(How to set dates and days automatically of the whole month in 1 to 30 (31, as per the month) cells in google script editior?)

Question

Here is the image showing dates and days of december.

(这是显示12月日期和日期的图像。)

I need to autobackup a sheet on the 1st date of every month, but in backup sheet created month and days should change to current ones.

(我需要在每月的第一天自动备份工作表，但是在创建的备份工作表中，月份和日期应该更改为当前日期。)

As in the image its december next month on the execution of trigger it should change 1 to 31 dates of january with days too in the next column as shown.

(如图所示，在下个月执行触发器时，它应该将下个月的1月1日更改为1月31日，并且在下一列中也将显示天，如图所示。)

(As per the month it should set dates till 30 or 31 , or 28 for feb).

(（根据月份，应将日期设置为30或31，或将2月设置为28）。)

My backup sheet creation part is working fine just i would like to know how to set this dates and days thing.

(我的备份表创建部分运行正常，只是我想知道如何设置日期和日期。)

  ask by Zil Sanghvi translate from so

Answer 1

Create This Month Sheet

(创建本月表)

The code:

(代码：)

function createThisMonth() {
  var ss=SpreadsheetApp.getActive();
  var shn=Utilities.formatDate(new Date(),Session.getScriptTimeZone() ,"MMM-yyyy");
  var sh=ss.getSheetByName(shn);
  if(!sh) {
    var sh=ss.insertSheet(shn,0);
  }
  var dA=[];
  var cA=[];
  var days=parseInt(Utilities.formatDate(new Date(new Date().getFullYear(),new  Date().getMonth()+1,new Date().getDate()-1), Session.getScriptTimeZone(), "d"));
  for(var d=1;d<=days;d++) {
    var c1=Utilities.formatDate(new Date(new Date().getFullYear(),new Date().getMonth(),d),Session.getScriptTimeZone() , "d-MMM-yyyy");
    var c2=Utilities.formatDate(new Date(new Date().getFullYear(),new Date().getMonth(),d),Session.getScriptTimeZone() , "E");
    var c9=Utilities.formatDate(new Date(new Date().getFullYear(),new Date().getMonth(),d),Session.getScriptTimeZone() , "HH:mm:ss");
    dA.push([c1,c2,0,0,0,0,0,0,c9,'']);
    cA.push(['#ffffff','#ffffff','#ffff00','#ffff00','#ffff00','#ffff00','#ffff00','#ffff00','#00ff00','#ff0000']);
  }
  sh.getRange(1,1,dA.length,dA[0].length).setValues(dA);
  sh.getRange(1,1,dA.length,dA[0].length).setBackgrounds(cA);
}

Run this to create trigger:

(运行此命令以创建触发器：)

function createTriggerForcreateTheMonth() {
  if(!isTrigger('createThisMonth')) {
    ScriptApp.newTrigger('createThisMonth').timeBased().onMonthDay(1).atHour(1).create();
  }
}

Helper function:

(辅助功能：)

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

The Sheet:

(工作表：)