-
-
Save cybozudevnet/5c05bddcb309c288ae43b3bb3ef6a8a3 to your computer and use it in GitHub Desktop.
kintone 見積書アプリからGoogleスプレッドシートで帳票出力してみよう! https://developer.cybozu.io/hc/ja/articles/115005283123
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
_ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* global gapi */ | |
/* global google */ | |
(() => { | |
'use strict'; | |
// API キー | |
const API_KEY = 'GOOGLE_API_KEY'; | |
// クライアントID | |
const CLIENT_ID = 'GOOGLE_CLIENT_ID'; | |
// スプレッドシートID | |
const SHEET_ID = 'GOOGLE_SHEET_ID'; | |
// ログイン状態監視用 | |
let tokenClient = false, | |
gapiInited = false, | |
gisInited = false; | |
// ログイン準備ができたらログインボタン表示 | |
const checkBeforeStart = () => { | |
if (gapiInited && gisInited) { | |
document.getElementById('login_button').style.visibility = 'visible'; | |
} | |
}; | |
const loadGapi = () => { | |
gapi.load('client', intializeGapiClient); | |
}; | |
const intializeGapiClient = async () => { | |
await gapi.client.init({ | |
apiKey: API_KEY, | |
discoveryDocs: ['https://www.googleapis.com/discovery/v1/apis/sheets/v4/rest'], | |
}); | |
gapiInited = true; | |
checkBeforeStart(); | |
}; | |
const loadGsi = () => { | |
tokenClient = google.accounts.oauth2.initTokenClient({ | |
client_id: CLIENT_ID, | |
scope: 'https://www.googleapis.com/auth/spreadsheets', | |
callback: '', // defined later | |
}); | |
gisInited = true; | |
checkBeforeStart(); | |
}; | |
// Googleログイン準備 | |
const initClient = () => { | |
loadGapi(); | |
loadGsi(); | |
}; | |
// Googleログインボタンクリック | |
const handleLoginClick = () => { | |
tokenClient.callback = async (resp) => { | |
if (resp.error !== undefined) { | |
throw resp; | |
} | |
document.getElementById('logout_button').style.visibility = 'visible'; | |
document.getElementById('print_button').style.visibility = 'visible'; | |
document.getElementById('login_button').textContent = 'Refresh'; | |
}; | |
if (gapi.client.getToken() === null) { | |
tokenClient.requestAccessToken({prompt: 'consent'}); | |
} else { | |
tokenClient.requestAccessToken({prompt: ''}); | |
} | |
}; | |
// ログアウト | |
const handleLogoutClick = () => { | |
const token = gapi.client.getToken(); | |
if (token !== null) { | |
google.accounts.oauth2.revoke(token.access_token); | |
gapi.client.setToken(''); | |
document.getElementById('login_button').textContent = 'Googleログイン'; | |
document.getElementById('logout_button').style.visibility = 'hidden'; | |
document.getElementById('print_button').style.visibility = 'hidden'; | |
} | |
}; | |
// 見積書印刷 | |
const printInvoice = (event) => { | |
// レコードのデータの取得 | |
const record = kintone.app.record.get().record; | |
if (!record) { | |
return; | |
} | |
// API リクエスト | |
// リクエストパラメータの設定 | |
const params = { | |
// スプレッドシートID | |
spreadsheetId: SHEET_ID | |
}; | |
// 商品リストをSpreadSheet用の配列に変更 | |
const productArray = record.見積明細.value.map((item) => [ | |
item.value.商品名.value, | |
null, | |
null, | |
null, | |
null, | |
item.value.数量.value, | |
item.value.単価.value, | |
]); | |
// スプレッドシートに出力するデータの設定 | |
const batchUpdateSpreadsheetRequestBody = { | |
'valueInputOption': 'RAW', | |
'data': [ | |
{ | |
'range': '\'Invoice\'!A1:A6', | |
'majorDimension': 'COLUMNS', | |
'values': [ | |
[ | |
record.宛名.value + '様', | |
record.会社名.value, | |
record.郵便番号.value, | |
record.都道府県.value, | |
record.住所.value, | |
record.電話番号.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!F5:H7', | |
'majorDimension': 'ROWS', | |
'values': [ | |
[ | |
record.見積番号.value, | |
null, | |
record.見積日.value | |
], | |
[], | |
[ | |
record.顧客番号.value, | |
null, | |
record.有効期限.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!A12:A17', | |
'majorDimension': 'COLUMNS', | |
'values': [ | |
[ | |
record.宛名.value + '様', | |
record.会社名.value, | |
record.郵便番号.value, | |
record.都道府県.value, | |
record.住所.value, | |
record.電話番号.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!F12:F17', | |
'majorDimension': 'COLUMNS', | |
'values': [ | |
[ | |
record.宛名.value + '様', | |
record.会社名.value, | |
record.郵便番号.value, | |
record.都道府県.value, | |
record.住所.value, | |
record.電話番号.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!A39:A39', | |
'majorDimension': 'COLUMNS', | |
'values': [ | |
[record.備考.value] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!A21:G38', | |
'majorDimension': 'ROWS', | |
'values': productArray | |
} | |
] | |
}; | |
// スプレッドシートへの出力実行 | |
const request = gapi.client.sheets.spreadsheets.values.batchUpdate(params, batchUpdateSpreadsheetRequestBody); | |
request.then((response) => { | |
alert('帳票出力に成功しました。'); | |
}, (reason) => { | |
alert('エラー: ' + reason.result.error.message); | |
}); | |
}; | |
// レコード詳細画面の表示後イベント | |
kintone.events.on('app.record.detail.show', (event) => { | |
// ボタン増殖回避 | |
if (document.getElementById('print_button') !== null) { | |
return event; | |
} | |
// Googleログインボタン作成 | |
const loginButton = document.createElement('button'); | |
loginButton.id = 'login_button'; | |
loginButton.textContent = 'Googleログイン'; | |
loginButton.style.visibility = 'hidden'; | |
// Googleログアウトボタン作成 | |
const logoutButton = document.createElement('button'); | |
logoutButton.id = 'logout_button'; | |
logoutButton.textContent = 'Googleログアウト'; | |
logoutButton.style.visibility = 'hidden'; | |
// 帳票出力ボタン作成 | |
const printButton = document.createElement('button'); | |
printButton.id = 'print_button'; | |
printButton.textContent = '帳票出力'; | |
printButton.style.visibility = 'hidden'; | |
// 各ボタンに機能を持たせる | |
logoutButton.addEventListener('click', handleLogoutClick); | |
loginButton.addEventListener('click', handleLoginClick); | |
printButton.addEventListener('click', () => { | |
printInvoice(event); | |
}); | |
// スペースフィールドに各ボタンを設置 | |
kintone.app.record.getSpaceElement('print_button_space').appendChild(loginButton); | |
kintone.app.record.getSpaceElement('print_button_space').appendChild(logoutButton); | |
kintone.app.record.getSpaceElement('print_button_space').appendChild(printButton); | |
// Googleログイン準備 | |
initClient(); | |
return event; | |
}); | |
})(); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* global gapi */ | |
/* global google */ | |
(() => { | |
'use strict'; | |
// API キー | |
const API_KEY = 'GOOGLE_API_KEY'; | |
// クライアントID | |
const CLIENT_ID = 'GOOGLE_CLIENT_ID'; | |
// スプレッドシートID | |
const SHEET_ID = 'GOOGLE_SHEET_ID'; | |
// ログイン状態監視用 | |
let tokenClient = false, | |
gapiInited = false, | |
gisInited = false; | |
// ログイン準備ができたらログインボタン表示 | |
const checkBeforeStart = () => { | |
if (gapiInited && gisInited) { | |
document.getElementById('login_button').style.visibility = 'visible'; | |
} | |
}; | |
const loadGapi = () => { | |
gapi.load('client', intializeGapiClient); | |
}; | |
const intializeGapiClient = async () => { | |
await gapi.client.init({ | |
apiKey: API_KEY, | |
discoveryDocs: ['https://www.googleapis.com/discovery/v1/apis/sheets/v4/rest'], | |
}); | |
gapiInited = true; | |
checkBeforeStart(); | |
}; | |
const loadGsi = () => { | |
tokenClient = google.accounts.oauth2.initTokenClient({ | |
client_id: CLIENT_ID, | |
scope: 'https://www.googleapis.com/auth/spreadsheets', | |
callback: '', // defined later | |
}); | |
gisInited = true; | |
checkBeforeStart(); | |
}; | |
// Googleログイン準備 | |
const initClient = () => { | |
loadGapi(); | |
loadGsi(); | |
}; | |
// Googleログインボタンクリック | |
const handleLoginClick = () => { | |
tokenClient.callback = async (resp) => { | |
if (resp.error !== undefined) { | |
throw resp; | |
} | |
document.getElementById('logout_button').style.visibility = 'visible'; | |
document.getElementById('print_button').style.visibility = 'visible'; | |
document.getElementById('login_button').textContent = 'Refresh'; | |
}; | |
if (gapi.client.getToken() === null) { | |
tokenClient.requestAccessToken({prompt: 'consent'}); | |
} else { | |
tokenClient.requestAccessToken({prompt: ''}); | |
} | |
}; | |
// ログアウト | |
const handleLogoutClick = () => { | |
const token = gapi.client.getToken(); | |
if (token !== null) { | |
google.accounts.oauth2.revoke(token.access_token); | |
gapi.client.setToken(''); | |
document.getElementById('login_button').textContent = 'Googleログイン'; | |
document.getElementById('logout_button').style.visibility = 'hidden'; | |
document.getElementById('print_button').style.visibility = 'hidden'; | |
} | |
}; | |
// 見積書印刷 | |
const printInvoice = (event) => { | |
// レコードのデータの取得 | |
const record = kintone.app.record.get().record; | |
if (!record) { | |
return; | |
} | |
// API リクエスト | |
// リクエストパラメータの設定 | |
const params = { | |
// スプレッドシートID | |
spreadsheetId: SHEET_ID | |
}; | |
// 商品リストをSpreadSheet用の配列に変更 | |
const productArray = record.見積明細.value.map((item) => [ | |
item.value.商品名.value, | |
null, | |
null, | |
null, | |
null, | |
item.value.数量.value, | |
item.value.単価.value, | |
]); | |
// スプレッドシートに出力するデータの設定 | |
const batchUpdateSpreadsheetRequestBody = { | |
'valueInputOption': 'RAW', | |
'data': [ | |
{ | |
'range': '\'Invoice\'!A1:A6', | |
'majorDimension': 'COLUMNS', | |
'values': [ | |
[ | |
record.宛名.value + '様', | |
record.会社名.value, | |
record.郵便番号.value, | |
record.都道府県.value, | |
record.住所.value, | |
record.電話番号.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!F5:H7', | |
'majorDimension': 'ROWS', | |
'values': [ | |
[ | |
record.見積番号.value, | |
null, | |
record.見積日.value | |
], | |
[], | |
[ | |
record.顧客番号.value, | |
null, | |
record.有効期限.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!A12:A17', | |
'majorDimension': 'COLUMNS', | |
'values': [ | |
[ | |
record.宛名.value + '様', | |
record.会社名.value, | |
record.郵便番号.value, | |
record.都道府県.value, | |
record.住所.value, | |
record.電話番号.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!F12:F17', | |
'majorDimension': 'COLUMNS', | |
'values': [ | |
[ | |
record.宛名.value + '様', | |
record.会社名.value, | |
record.郵便番号.value, | |
record.都道府県.value, | |
record.住所.value, | |
record.電話番号.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!A39:A39', | |
'majorDimension': 'COLUMNS', | |
'values': [ | |
[record.備考.value] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!A21:G38', | |
'majorDimension': 'ROWS', | |
'values': productArray | |
} | |
] | |
}; | |
// スプレッドシートへの出力実行 | |
const request = gapi.client.sheets.spreadsheets.values.batchUpdate(params, batchUpdateSpreadsheetRequestBody); | |
request.then((response) => { | |
alert('帳票出力に成功しました。'); | |
}, (reason) => { | |
alert('エラー: ' + reason.result.error.message); | |
}); | |
}; | |
// レコード詳細画面の表示後イベント | |
kintone.events.on('app.record.detail.show', (event) => { | |
// ボタン増殖回避 | |
if (document.getElementById('print_button') !== null) { | |
return event; | |
} | |
// Googleログインボタン作成 | |
const loginButton = document.createElement('button'); | |
loginButton.id = 'login_button'; | |
loginButton.textContent = 'Googleログイン'; | |
loginButton.style.visibility = 'hidden'; | |
// Googleログアウトボタン作成 | |
const logoutButton = document.createElement('button'); | |
logoutButton.id = 'logout_button'; | |
logoutButton.textContent = 'Googleログアウト'; | |
logoutButton.style.visibility = 'hidden'; | |
// 帳票出力ボタン作成 | |
const printButton = document.createElement('button'); | |
printButton.id = 'print_button'; | |
printButton.textContent = '帳票出力'; | |
printButton.style.visibility = 'hidden'; | |
// 各ボタンに機能を持たせる | |
logoutButton.addEventListener('click', handleLogoutClick); | |
loginButton.addEventListener('click', handleLoginClick); | |
printButton.addEventListener('click', () => { | |
printInvoice(event); | |
}); | |
// スペースフィールドに各ボタンを設置 | |
kintone.app.record.getSpaceElement('print_button_space').appendChild(loginButton); | |
kintone.app.record.getSpaceElement('print_button_space').appendChild(logoutButton); | |
kintone.app.record.getSpaceElement('print_button_space').appendChild(printButton); | |
// Googleログイン準備 | |
initClient(); | |
return event; | |
}); | |
})(); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// API キー | |
var api_key = 'GOOGLE_API_KEY'; | |
// クライアントID | |
var client_id = 'GOOGLE_CLIENT_ID'; | |
// スプレッドシートID | |
var sheet_id = 'GOOGLE_SHEET_ID'; | |
// 認証用URL(読み取り) | |
var scope = 'https://www.googleapis.com/auth/spreadsheets'; | |
// Discovery Docs | |
var discovery_docs = ['https://www.googleapis.com/discovery/v1/apis/sheets/v4/rest']; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function initClient() { | |
gapi.client.init({ | |
'apiKey': api_key, | |
'discoveryDocs': discovery_docs, | |
'clientId': client_id, | |
'scope': scope | |
}).then(function() { | |
// Google認証済みのチェック | |
if (!gapi.auth2.getAuthInstance().isSignedIn.get()) { | |
// Google認証の呼び出し | |
gapi.auth2.getAuthInstance().signIn(); | |
} | |
}); | |
} | |
// APIクライアントとOauth2モジュールのロード | |
// モジュールロード後のinitClient関数の呼び出し | |
gapi.load('client:auth2', { | |
callback: function() { | |
// gapi.clientのイニシャライズ処理 | |
initClient(); | |
}, | |
onerror: function() { | |
// エラー時の処理 | |
alert('gapi.client のロードに失敗しました!'); | |
} | |
}); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
kintone.events.on('app.record.detail.show',function(event){ }); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// スペースにボタンを設置 | |
var printButton = document.createElement('button'); | |
printButton.id = 'print_button'; | |
printButton.innerHTML = '帳票出力'; | |
printButton.className = 'button-simple-cybozu geo-search-btn'; | |
printButton.style = 'margin-top: 30px; margin-left: 10px;'; | |
printButton.addEventListener('click', function() { | |
printInvoice(event); | |
}); | |
kintone.app.record.getSpaceElement('print_button_space').appendChild(printButton); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Google認証済みのチェック | |
if(!gapi.auth2.getAuthInstance().isSignedIn.get()){ | |
// Google認証の呼び出し | |
gapi.auth2.getAuthInstance().signIn(); | |
alert('Google認証されていません。'); | |
return; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var params = { | |
// スプレッドシートID | |
spreadsheetId: sheet_id | |
}; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var record = kintone.app.record.get().record; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 商品リストをArray変数にセット | |
var productArray = []; | |
$.each(record.見積明細.value, function(i, item) { | |
productArray.push([ | |
item.value.product_name.value, | |
null, | |
null, | |
null, | |
null, | |
item.value.数量.value, | |
item.value.単価.value | |
]); | |
}); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// スプレッドシートに出力するデータの設定 | |
var batchUpdateSpreadsheetRequestBody = { | |
'valueInputOption': 'RAW', | |
'data': [ | |
{ | |
// 値を出力するシート範囲を設定します。(’シート名’!開始コラム:終了コラム) | |
'range': '\'Invoice\'!A1:A6', | |
// 値を指定する方向を設定します。(ROWS | COLUMNS) | |
'majorDimension': 'COLUMNS', | |
// majorDimessionにCOLUMNS を指定したので、 値の設定は、列ごとに行います。 | |
'values': [ | |
[ | |
record.customer_name.value + '様', | |
record.company_name.value, | |
record.postal_code.value, | |
record.city.value, | |
record.address.value, | |
record.phone.value | |
] | |
] | |
}, | |
{ | |
'range': '\'Invoice\'!F5:H7', | |
'majorDimension': 'ROWS', | |
// ROWSを指定した場合、次のように列ごとに値を設定します。 'values' : [ [A1データ,B1データ], [A2データ,B2データ] ] | |
'values': [ | |
[ | |
record.quote_no.value, | |
null, | |
record.date.value | |
], | |
[], | |
[ | |
record.customer_id.value, | |
null, | |
record.valid_until.value | |
] | |
] | |
}, | |
~~~ | |
] | |
}; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// スプレッドシートへの出力実行 | |
var request = gapi.client.sheets.spreadsheets.values.batchUpdate(params, batchUpdateSpreadsheetRequestBody); | |
request.then(function(response) { | |
alert('帳票出力に成功しました。'); | |
}, function(reason) { | |
alert('エラー: ' + reason.result.error.message); | |
}); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment