> For the complete documentation index, see [llms.txt](https://javascriptuse.gitbook.io/advanced/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://javascriptuse.gitbook.io/advanced/kintone-width-appscript-ok.md).
# Kintone width Appscript (ok)
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
```
```
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);
}
```
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://javascriptuse.gitbook.io/advanced/kintone-width-appscript-ok.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.