Kintone width Appscript (ok)
ShowForm.gs
Form.html
SaveData.gs
Last updated
ShowForm.gs
Form.html
SaveData.gs
Last updated
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');
}<!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> 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);
}