Kintone width Appscript (ok) | Advanced

ShowForm.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Kintone Menu')
      .addItem('Kintoneから データー集計','showForm')
      .addToUi();
}
function showForm() {
  const form = HtmlService.createTemplateFromFile('Form').evaluate().setHeight(180);
  SpreadsheetApp.getUi().showModalDialog(form, 'Request Data Window');
}

Form.html

<!DOCTYPE html>
<html>
	<head>
		<meta name="viewport" content="width=device-width, initial-scale=1.0" />
		<link rel="stylesheet" href="https://code.jquery.com/ui/1.13.1/themes/base/jquery-ui.css">
		<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
		<script type="text/javascript" src="https://momentjs.com/downloads/moment.js"></script>
		<script src="https://code.jquery.com/ui/1.13.1/jquery-ui.js"></script>
	</head>
	<body>
		<form>
			<label for="dueDate">Due Date</label>
			<input id="dueDate" class="datepicker">
			<button id="btn"> Submit </button>
		</form>
		<script type="text/javascript">
			function getAllDaysInMonth(year, month) {
				const date = new Date(year, month, 1);
				const dates = [];
				while (date.getMonth() === month) {
					dates.push(new Date(date));
					date.setDate(date.getDate() + 1);
				}
				return dates;
			}
      function writeResponse() {
				google.script.host.close();
			}
			document.getElementById('btn').addEventListener('click', getData);
			async function getData() {
				var data = document.forms[0]
			     var id = data[0].value;
			     var mydate = id.split('/');
			     if(mydate.length == 1) {
			     	var mydate = id.split('-');
			     }
			    var arrform = [];
          var arrDay = [];
					var arrdats = getAllDaysInMonth(parseInt(mydate[0]),(parseInt(mydate[1]) - 1));
					arrdats.forEach(function(element, index) {
						arrform.push(moment(element).format('MM/DD'));
            arrDay.push(moment(element).day())
					});
				 google.script.run.saveData(id,arrform, arrDay);
         await writeResponse();
			}
			$( function() {
			   $( "#dueDate" ).datepicker({ dateFormat: 'yy/mm' }).val();
			 } );
		</script>
	</body>
</html>

SaveData.gs

var KintoneManager = (function() {
  /**
   * @param {string} subdomain your subdomain (For kintone.com domains, you must state the FQDN such as "subdomain.kintone.com" )
   * @param {object} apps application information.
   * @param {string} user (optional) user name or encoded authentication information: base64("USER:PASS")
   * @param {string} pass (optional) password
   * @constructor
   */
  function KintoneManager(subdomain, apps, user, pass) {
    this.subdomain = subdomain;
    this.authorization = null;
    this.apps = apps;
    if (arguments.length > 3) {
      this.authorization = Utilities.base64Encode(user + ":" + pass);
    } else if (arguments.length > 2) {
      // 引数が3つの場合はエンコード済みの認証情報として処理
      this.authorization = user;
    }
  }
  /**
   * Constructor
   * @param {string} app_name Application name
   * @param {Array} records Kintone record objects ref) https://developer.cybozu.io/hc/ja/articles/201941784
   * @returns {HTTPResponse} ref) https://developers.google.com/apps-script/reference/url-fetch/http-response
   */
  KintoneManager.prototype.create = function(app_name, records) {
    var app = this.apps[app_name];
    var payload = {
      app: app.appid,
      records: records
    };
    var response = UrlFetchApp.fetch("@1/records.json".replace(/@1/g, this._getEndpoint(app.guestid)), this._postOption(app, payload));
    return response;
  };
  /**
   * Search records
   * @param {string} app_name Application name
   * @param {string} query kintone API query ref) https://developer.cybozu.io/hc/ja/articles/202331474-%E3%83%AC%E3%82%B3%E3%83%BC%E3%83%89%E3%81%AE%E5%8F%96%E5%BE%97-GET-#step2
   * @returns {Array} search results
   */
  KintoneManager.prototype.search = function(app_name, query) {
    var q = encodeURIComponent(query);
    var app = this.apps[app_name];
    var response = UrlFetchApp.fetch("@1/records.json?app=@2&query=@3&totalCount=true".replace(/@1/g, this._getEndpoint(app.guestid)).replace(/@2/g, app.appid).replace(/@3/g, q), this._getOption(app));
    return response;
  };
  /**
   * Updates records
   * @param {string} app_name Application name
   * @param {Array} records Array of records that will be updated.
   * @returns {HTTPResponse} ref) https://developers.google.com/apps-script/reference/url-fetch/http-response
   */
  KintoneManager.prototype.update = function(app_name, records) {
    var app = this.apps[app_name];
    var payload = {
      app: app.appid,
      records: records
    };
    var response = UrlFetchApp.fetch("@1/records.json".replace(/@1/g, this._getEndpoint(app.guestid)), this._putOption(app, payload));
    return response;
  };
  /**
   * Deletes Records
   * @param {string} app_name Application name
   * @param {Array} record_ids Array of record IDs that will be deleted.
   * @returns {HTTPResponse} ref) https://developers.google.com/apps-script/reference/url-fetch/http-response
   */
  KintoneManager.prototype.destroy = function(app_name, record_ids) {
    var app = this.apps[app_name];
    var query = "app=" + app.appid;
    for (var i = 0; i < record_ids.length; i++) {
      query += "&ids[@1]=@2".replace(/@1/g, i).replace(/@2/g, record_ids[i]);
    }
    var response = UrlFetchApp.fetch("@1/records.json?@2".replace(/@1/g, this._getEndpoint(app.guestid)).replace(/@2/g, query), this._deleteOption(app));
    return response;
  };
  /**
   * option for GET Method
   * @param {object} app Application object
   * @returns {object} Option for UrlFetchApp
   * @private
   */
  KintoneManager.prototype._getOption = function(app) {
    var option = {
      method: "get",
      headers: this._authorizationHeader(app),
      muteHttpExceptions: true
    };
    return option;
  };
  /**
   * option for POST Method
   * @param {object} app Application object
   * @param {object} payload Request payload
   * @returns {object} Option for UrlFetchApp
   * @private
   */
  KintoneManager.prototype._postOption = function(app, payload) {
    var option = {
      method: "post",
      contentType: "application/json",
      headers: this._authorizationHeader(app),
      muteHttpExceptions: true,
      payload: JSON.stringify(payload)
    };
    return option;
  };
  /**
   * option for PUT Method
   * @param {object} app Application object
   * @param {object} payload Request payload
   * @returns {object} Option for UrlFetchApp
   * @private
   */
  KintoneManager.prototype._putOption = function(app, payload) {
    var option = {
      method: "put",
      contentType: "application/json",
      headers: this._authorizationHeader(app),
      muteHttpExceptions: true,
      payload: JSON.stringify(payload)
    };
    return option;
  };
  /**
   * option for DELETE Method
   * @param {object} app Application Object
   * @returns {object} option Option for UrlFetchApp
   * @private
   */
  KintoneManager.prototype._deleteOption = function(app) {
    var option = {
      method: "delete",
      headers: this._authorizationHeader(app),
      muteHttpExceptions: true
    };
    return option;
  };
  /**
   * Gets Endpoint
   * @param {string} guest_id (optional) Guest id if you are a guest account.
   * @returns {string} Endpoint url
   * @private
   */
  KintoneManager.prototype._getEndpoint = function(guest_id) {
    if (this.subdomain.slice(-4) == '.com') {
      var endpoint = "https://@1".replace(/@1/g, this.subdomain);
    } else {
      var endpoint = "https://@1.cybozu.com".replace(/@1/g, this.subdomain);
    }
    if (guest_id == null) {
      return endpoint + "/k/v1";
    } else {
      return endpoint + "/k/guest/@1/v1".replace(/@1/g, guest_id);
    }
  };
  /**
   * Header Authentication Information
   * @param {object} app Application object
   * @param {string} app.token Application's API token
   * @returns {object}
   * @private
   */
  KintoneManager.prototype._authorizationHeader = function(app) {
    if (this.authorization) {
      // Password authentication
      return { "X-Cybozu-Authorization": this.authorization };
    } else if (app.token) {
      // API token authentication
      return { "X-Cybozu-API-Token": app.token };
    } else {
      throw new Error("Authentication Failed");
    }
  };
  return KintoneManager;
})();
function uniqByKeepFirst(a, key) {
  let seen = new Set();
  return a.filter(item => {
    let k = key(item);
    return seen.has(k) ? false : seen.add(k);
  });
}
function dateFormat(inputDate, format) {
  const date = new Date(inputDate);
  const day = date.getDate();
  const month = date.getMonth() + 1;
  const year = date.getFullYear();
  format = format.replace("MM", month.toString().padStart(2, "0"));
  if (format.indexOf("yyyy") > -1) {
    format = format.replace("yyyy", year.toString());
  } else if (format.indexOf("yy") > -1) {
    format = format.replace("yy", year.toString().substr(2, 2));
  }
  format = format.replace("dd", day.toString().padStart(2, "0"));
  return format;
}
function thayDoi(data, pi2) {
  // var ui = SpreadsheetApp.getUi();
  // ui.alert(JSON.stringify(data));
  return data.reduce((acc, item) => {
    acc[item.name] = [item];
    acc[item.name] = [];
    pi2.forEach(function(element2, index2) {
      if (item.name == element2.name) {
        data.forEach(function(element, index) {
          if (item.name == element.name) {
            acc[item.name].push(element);
          }
        });
      }
    });
    return acc;
  }, {});
}
const getAllDaysInMonth = (month, year) => Array.from({
    length: new Date(year, month, 0).getDate()
  },
  (_, i) => new Date(year, month - 1, i + 1));
function docGhiMang1(datas, lenkey, days, boole = false) {
  var i = 3;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();

  if (boole) {
    if(lastRow) {
      sheet.deleteRows(1, lastRow);
    }
    for (var k = 1; k < 30; k++) {
      for (j = 0; j < days.length; j++) {
        sheet.getRange(k, j + 2).setBackground("white");
        if (days[j] == 0 && k == 2) {
          sheet.getRange(k, j + 2).setBackground("#FF1493");
        }
        if (days[j] == 6 && k == 2) {
          sheet.getRange(k, j + 2).setBackground("#63B8FF");
        }
      }
    }
  } else {
    for (key in datas) {
      var valus = [];
      var len = datas[key].length + 2;
      var lenkeya = lenkey + 2;
      var cell = sheet.getRange(1, 1, lenkeya, len);
      cell.setHorizontalAlignment("center");
      cell.setBorder(true, true, true, true, true, true, "#000000", SpreadsheetApp.BorderStyle.SOLID);
      var dates = ["民名"];
      valus.push(key);
      var total = 0;
      var string = '';
      datas[key].forEach(function(element, index) {
        string = element.month;
        dates.push(element.date);
        valus.push(element.number);
        if (!isNaN(Number(element.number))) {
          total += Number(element.number);
        }
      });
      string += "月度";
      dates.push("Total");
      valus.push(total);
      var dich0 = sheet.getRange(1, 1);
      dich0.setValue(string);
      var dich1 = sheet.getRange(2, 1, 1, len);
      dich1.setValues([dates]);
      var dich2 = sheet.getRange(i, 1, 1, len);
      dich2.setValues([valus]);
      i++;
    }
  }
}
function saveData(id, arrform, arrDay) {
  var ui = SpreadsheetApp.getUi();
  var subdomain = "genkaitec1.cybozu.com";
  var apps = {
    YOUR_APPLICATION1: { appid: 20, token: "NXvOuhG5WPpexePRTs1OGdDOHcxYtQpE9TKbDYSS" }
  };
  var user = "kaori_aikawa@namura.co.jp";
  var pass = "a00d0pd0kinton12";
  var kintone_manager = new KintoneManager(subdomain, apps, user, pass);
  var query = 'レコード番号 > 0'
  var response = kintone_manager.search('YOUR_APPLICATION1', query);
  var code = response.getResponseCode();
  var content = JSON.parse(response.getContentText());
  var records = content.records;
  var datas = [];
  var dates = [];
  var valus = [];
  var datest = [];
  var valuss = [];
  var number = 0;
  var id = id.replace(/\//g, "-");
  var datein = dateFormat(JSON.stringify(id), 'yyyy-MM');
  var ardatm = datein.split("-");
  var newyear = ardatm[0];
  var newmon = ardatm[1];
  var iddat = id.split("-");
  records.forEach(function(element, index) {
    var idnam = element.日付.value.split("-");
    if (iddat[0] == idnam[0] && iddat[1] == idnam[1]) {
      datas.push({ date: dateFormat(element.日付.value, 'MM/dd'), number: element.集計用.value, name: element.作成者.value.name });
    }
  });
  var dataGetKey = uniqByKeepFirst(datas, it => it.name);
  var lenKey = dataGetKey.length;
  var dataLast = [];
  var piar;
  var pi = thayDoi(datas, dataGetKey);
  var arrformd = {};
  var number = '';
  dataGetKey.forEach(function(element, index3) {
    function userExists(username) {
      return pi[element.name].some(function(el) {
        return el.date == username;
      });
    }
    if (pi.hasOwnProperty(element.name)) {
      var j = 0;
      arrformd[element.name] = [];
      arrform.findIndex(function(currentValue, index) {
        pi[element.name].sort(function(a, b) {
          var c = new Date(a.date);
          var d = new Date(b.date);
          return c - d;
        });
        if (userExists(currentValue)) {
          number = pi[element.name][j].number;
          j++;
        } else {
          number = '';
        }
        arrformd[element.name].push({ month: newmon, date: currentValue.toString(), number: number });
      });
    }
  });
  docGhiMang1(arrformd, lenKey, arrDay, true);
  docGhiMang1(arrformd, lenKey, arrDay);
}