この記事ではGoogleフォームの回答をスプレッドシートに記録し、そのデータをGASとkintoneAPIを使用してkintoneアプリに登録する方法をご紹介します。
目次
今回作成したシステムの概要
Googleフォームに回答します。(今回は経費精算を想定してフォームを作成しています)
Googleフォームに回答すると、フォームと連携したスプレッドシートに回答が記載されていきます。ここでは「経費精算」シートに記載されています。
回答が集まったらkintoneに登録していきます。手動でkintoneに登録する場合は、スプレッドシートのメニューバーにある「kintone連携」をクリック。表示された「kintoneに登録」をクリックします。
すると、kintoneのアプリに回答が登録されます。
また「経費精算」シート内の回答データは「アーカイブ」シートにコピーされます。(下図の黄色行が移動した回答です)「アーカイブ」シートにコピー後、「経費精算」シートの回答はクリアされます。
またGoogleフォームの回答をkintoneに登録する処理を、時間を決めて自動実行することも可能です。自動実行させる場合はトリガーを設定します（設定方法は後述）。
kintoneアプリ作成
Googleフォームの回答データを登録するkintoneアプリを作成します。
設定タブにてAPIトークンを取得します。今回はレコード追加にチェックを入れます。
Googleフォームと、連携するスプレッドシートを作成
Googleフォームを作成します。Googleフォームを作成後、回答タブにあるスプレッドシートのアイコンをクリックし、スプレッドシートを新規作成します。
Googleフォームと連携したスプレッドシートが開きます。Googleフォームと連携しているシートにはGoogleフォームのアイコンが表示され、先頭行にはGoogleフォームに設定した質問が入力されています。
ここでデータ退避用シートを追加します。シート名は「アーカイブ」とします。またGoogleフォームと連携しているシート名を「経費精算」に変更します。
kintoneManagerライブラリを取得
スプレッドシートの拡張機能メニューよりAppsScript(GAS)を開きます。エディタが開いたら、GASからkintoneAPIを呼び出せるライブラリを追加します。ライブラリの追加方法は、「KintoneManager」を公開している記事Qiita Tip: kintone とGoogle Apps Script連携に記載されています。
GASの作成
GASの作成に入ります。以下はスプレッドシートのデータを取得する処理のコードになります。
//スプレッドシートメニュー作成
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('kintone連携');
menu.addItem('kintoneに登録','sendExpensesData');
menu.addToUi();
}
// Googleフォームと連携したシートからデータ取得・kintone登録
function sendExpensesData() {
//スプレッドシートの情報取得
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシート情報取得
const exSheet = spreadsheet.getSheetByName('経費精算'); //Googleフォームからの回答を記録したシート
const values = exSheet.getDataRange().getValues(); //経費精算シートのデータ
let lastRow = exSheet.getLastRow(); //経費精算シートの最終行
const acSheet = spreadsheet.getSheetByName('アーカイブ'); //アーカイブ用シート
let acStartRow = acSheet.getLastRow() + 1; //アーカイブ用シートの書き込み開始行
let acLastCol = acSheet.getLastColumn(); //アーカイブ用シートの最終カラム
let valcopy = []; //アーカイブシートコピー用配列
let insertData = '['; // kintone登録用データ
//kintone登録用データ作成
for(i in values) {
//1行目のヘッダ行はスキップ
if(i == 0) {
continue;
};
//タイムスタンプ(yyyy/mm/dd HH:mm:ss)は「yyyy-MM-ddTHH:mm:ss+09:00」の形式にする
insertData += Utilities.formatString('{"timestamp": {"value": "%s"}', Utilities.formatDate(values[i][0], 'Asia/Tokyo', 'yyyy-MM-dd')
+ 'T' + Utilities.formatDate(values[i][0], 'Asia/Tokyo', 'HH:mm:ss') + '+09:00'); //タイムスタンプ
insertData += ',' + Utilities.formatString('"Email": {"value": "%s"}', values[i][1]); //メールアドレス
insertData += ',' + Utilities.formatString('"name": {"value": "%s"}', values[i][2]); //名前
insertData += ',' + Utilities.formatString('"recDate": {"value": "%s"}',
Utilities.formatDate(values[i][3], 'Asia/Tokyo', 'yyyy-MM-dd')); //該当日
insertData += ',' + Utilities.formatString('"category": {"value": "%s"}', values[i][4]); //種類
insertData += ',' + Utilities.formatString('"title": {"value": "%s"}', values[i][5]); //題名
insertData += ',' + Utilities.formatString('"reason": {"value": "%s"}', values[i][6]); //購入理由
insertData += ',' + Utilities.formatString('"lineSta": {"value": "%s"}', values[i][7]); //路線・駅名
insertData += ',' + Utilities.formatString('"receipt": {"value": "%s"}', values[i][8]); //領収書の有無
insertData += ',' + Utilities.formatString('"amount": {"value": "%s"}', values[i][9]); //精算金額
insertData += ',' + Utilities.formatString('"client": {"value": "%s"}}', values[i][10]); //請求クライアント名
//最終行でなければカンマ追加
if(i < lastRow -1) {
insertData += ',';
}
//アーカイブシートコピー用配列にデータ格納
valcopy.push(values[i]);
}
insertData += ']';
//kintoneに登録
let code = sendToKintone(insertData);
//エラーコード取得：200であれば登録成功
if(code != 200) {
if(code == 0) {
console.log("取り込むデータがありません"); //スプレッドシートにデータが登録されていない場合
} else {
console.log("kintone登録処理にてエラーが発生しました");
}
return;
}
//kintone登録後の処理：読み込みした経費精算データをアーカイブシートに追加
acSheet.getRange(acStartRow, 1, valcopy.length, acLastCol).setValues(valcopy); //アーカイブシートのデータ最終行の次の行にコピー
//kintone登録後の処理：読み込みした経費精算データをフォーム連携シートから削除
exSheet.deleteRows(2, lastRow); //2行目からデータ最終行まで行削除
}スプレッドシートのメニュー作成
スプレッドシートに記載された回答をkintoneに登録するメニューを作成します。スプレッドシートを開いた時にメニューが表示されるよう、onOpenというイベントハンドラを定義します。onOpen内に記載された処理はスプレッドシートを開いた時に実行されます。
function onOpen() {
//スプレッドシートを開いた時に実行したい処理を記載
}ここでは、スプレッドシートが開いたときに、独自のメニューを作成する処理を記載します。メニュー名とボタン名はスプレッドシートに表示させたい任意の名前を指定し、関数名にはボタン押下時に実行させたい関数名(function名)を指定します。
//スプレッドシートメニュー作成
function onOpen() {
const ui = SpreadsheetApp.getUi(); //スプレッドシートのインターフェースを取得
const menu = ui.createMenu('【メニュー名】'); //メニュー名
menu.addItem('【ボタン名】','【実行する関数名】'); //ボタン名と実行する関数を指定
menu.addToUi(); //スプレッドシートに反映
}フォームと連携するシートからデータを取得
スプレッドシート情報を取得する処理は以下になります。ここでは指定したシートのデータ行、データの最終行を取得します。
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシート情報取得
const exSheet = spreadsheet.getSheetByName('【フォームと連携中のシート名】');
const values = exSheet.getDataRange().getValues(); //フォーム連携シートのデータ取得
let lastRow = exSheet.getLastRow(); //データの最終行次にフォームと連携中のシートから取得したデータを文字列変数に格納していきます。併せてアーカイブ登録用の配列にデータを格納していきます。
let valcopy = []; //アーカイブシートコピー用配列
let insertData = '['; // kintone登録用データ
//kintone登録用データ作成
for(i in values) {
//1行目のヘッダ行はスキップ
if(i == 0) {
continue;
};
insertData += Utilities.formatString(
'"【kintoneアプリのフィールドコード】": {"value": "%s"}', values[i][0]);
insertData += ',' + Utilities.formatString(
'"【kintoneアプリのフィールドコード】": {"value": "%s"}', values[i][1]);
// -------------- 中略 -----------------
insertData += ',' + Utilities.formatString(
'"【kintoneアプリのフィールドコード】": {"value": "%s"}}', values[i][最終カラム]);
//最終行でなければカンマ追加
if(i < lastRow -1) {
insertData += ',';
}
//アーカイブシートコピー用配列にデータ格納
valcopy.push(values[i]);
}
insertData += ']';
日付・日時を指定する場合は”Utilities.formatDate”を使用します。
//日付の場合(YYYY-MM-DD形式にする)
insertData += Utilities.formatString('"【日付フィールドコード】": {"value": "%s"}',
Utilities.formatDate(【値(日付)】, 'Asia/Tokyo', 'yyyy-MM-dd'));
//日時の場合(YYYY-MM-DDTHH:mm:ss+09:00形式にする)
insertData +=
Utilities.formatString('{"【日時フィールドコード】": {"value": "%s"}',
Utilities.formatDate(【値(日付)】, 'Asia/Tokyo', 'yyyy-MM-dd')+ 'T'
+ Utilities.formatDate(【値(日付)】, 'Asia/Tokyo', 'HH:mm:ss')+'+09:00');kintoneに登録する日時の形式
kintoneの日時フィールドにデータを登録する場合は「YYYY-MM-DDTHH:MM:SSZ」の形式に設定する必要があります。また日本時間（JST）を表すには、登録する日時データに+9時間を追加する形で指定する必要があります。
繰り返し処理の内容は、カラムごとにデータを読み込み文字列変数に追加していきます。文字列配列は下図の並びになるように登録します。
//kintone登録用データ（文字列）
let str = '
[
//１行目のレコード
{
"【kintoneのフィールドコード】" : {"value": "【１カラム目の値】"},
"【kintoneのフィールドコード】" : {"value": "【２カラム目の値】"},
・ ・ ・
"【kintoneのフィールドコード】" : {"value": "【最終カラムの値】"}
},
//２行目のレコード
{
"【kintoneのフィールドコード】" : {"value": "【１カラム目の値】"},
"【kintoneのフィールドコード】" : {"value": "【２カラム目の値】"},
・ ・ ・
"【kintoneのフィールドコード】" : {"value": "【最終カラムの値】"}
},
・ ・ ・
・ ・ ・
・ ・ ・
//最終レコード
{
"【kintoneのフィールドコード】" : {"value": "【１カラム目の値】"},
"【kintoneのフィールドコード】" : {"value": "【２カラム目の値】"},
・ ・ ・
"【kintoneのフィールドコード】" : {"value": "【最終カラムの値】"}
}
]';kintoneに登録
kintone格納データを作成したらkintoneへの登録処理に移ります。
//kintoneに登録（【】の中をそれぞれの環境に応じて書き換える）
function sendToKintone(insertData) {
//kintone情報
const appId = '【アプリID】'; //アプリID
const appName = '【アプリ名】'; //アプリ名
const token = '【APIトークン】'; //APIトークン
const subdomain = '【サブドメイン】'; //サブドメイン(.cybozu.comの前のURL)
let code = 0; //リターンコード
let records = JSON.parse(insertData); //kintone登録データをJSON形式に変換
//データがなければリターン。リターンコード：0を返す
if(!records.length) {
return code;
}
//アプリID、アプリ名、APIトークンを指定
const apps = { YOUR_APPLICATION1 : {appid : appId, name : appName,token : token}}
// kintoneライブラリ初期化
const manager = new KintoneManager.KintoneManager(subdomain, apps);
// kintoneにデータを登録
const response = manager.create("YOUR_APPLICATION1", records);
// ログ出力
Logger.log('response => "%s"', response);
code = response.getResponseCode(); //kintoneからのリターンコード
Logger.log('Response code is "%s"', code);
return code;
}kintoneに登録する前に、以下の処理で引数として取得したデータをJSON形式に変更します。
let records = JSON.parse(insertData); //kintone登録データをJSON形式に変換kintoneライブラリの初期化を実行後、指定したkintone情報をもとにkintoneアプリにデータを登録します。kintoneからのリターンコードが200の場合は登録処理成功です。
// kintoneライブラリ初期化
const manager = new KintoneManager.KintoneManager(subdomain, apps);
// kintoneにデータを登録
const response = manager.create("YOUR_APPLICATION1", records);
フォームの回答をアーカイブ
Googleフォーム連携シートに入力された回答をアーカイブ用シートに移動します。
//Googleフォーム連携シートのデータをアーカイブシートに追加
acSheet.getRange(【データ最終行+1】,1,【追加データの件数】,【データ最終カラム】).setValues(【追加データ】);Googleフォーム連携シートのデータが、アーカイブシートの下図赤枠に追加されます。
Googleフォーム連携シートに入力された回答データを行削除します。
//Googleフォーム連携シートのデータを削除
exSheet.deleteRows(2,【Googleフォーム連携シートのデータ最終行】);
自動実行できるようにトリガーを設定
スプレッドシートのデータをkintone に登録する処理を自動で行えるよう、GASにトリガーを設定します。GASの画面左側のメニューよりトリガー(時計アイコン)をクリックします。
トリガー一覧画面が表示されます。新規に作成する場合は画面右下の「トリガーを追加」ボタンを押下します。
トリガー設定画面が表示されます。ここでスクリプトの実行条件を設定していきます。「実行する関数を選択」の欄では自動実行させたいスクリプト名を指定します。「イベントのソースを選択」の欄では【スプレッドシートから】・【時間主導型】・【カレンダーから】の何れかを指定します。ここではそれぞれ下記のように設定します。
実行する関数を選択：sendExpensesData(スプレッドシートからデータを取得する関数)
イベントのソースを選択：時間主導型
「イベントのソースを選択」にて選択した内容によって設定項目が変わります。ここでは時間主導型を選択し、下図の通り毎月1日 午前0時に実行するように設定します。
設定が終わったら「保存」ボタンを押下し、トリガーを保存します。
「保存」ボタン押下後、作成したトリガーが一覧に表示されます。
作成済みのトリガーを編集する場合は一覧右側の編集ボタン(ペンアイコン)、トリガーの内容を確認・削除する場合は3点リーダーをクリックします。
おわりに
今回はGoogleフォームの回答をスプレッドシートに記録し、そのデータをGASを利用してkintoneに登録する手順をご紹介しました。
尚、Googleフォームの回答をスプレッドシートを介さずに、フォーム送信時に直接kintoneに登録することも可能です。Googleフォームの回答をkintoneに登録する方法はcybozu developer network：Googleフォームとkintoneを連携してみよう！に記載されています。そちらも確認してみて下さい。
