kintoneレコードをスプレッドシートに転記(GAS)

ゆーしゃん
2022.12.25
1,550 views

概要

kintoneアプリの一覧で表示されているレコードをスプレッドシートに転記する

※動画では「在籍中」一覧(従業員が退職していない)に表示されている128レコードをスプレッドシートに転記

プログラムを作成した理由

とある業務にて、
下記4のチェックGASは自動化されていたが、下記1～3は手動となっている業務フローがあった。

対象kintoneアプリの●●一覧を表示
CSVとしてダウンロード
ダウンロードしたデータをスプレッドシートに貼り付け
「ルールに逸脱したレコードが入力されていないか」のチェックGAS実行

仕事に空き時間があった時に、スプレッドシートに転記ってそもそもGASで出来ないのかな…？
と気になった為。

ソースコード

/*
概要：指定クエリーで取得出来た全レコードをスプレッドシートに反映

参考URL：
・【公式】レコードの一括取得（クエリで条件を指定）
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-

・kintoneのデータをスプレッドシートに取得する
https://specially198.com/get-kintone-data-into-a-spreadsheet/

・レコード一覧を指定したkintoneのデータを、スプレッドシートに取得したい
https://developer.cybozu.io/hc/ja/community/posts/6021383965849-%E3%83%AC%E3%82%B3%E3%83%BC%E3%83%89%E4%B8%80%E8%A6%A7%E3%82%92%E6%8C%87%E5%AE%9A%E3%81%97%E3%81%9Fkintone%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92-%E3%82%B9%E3%83%97%E3%83%AC%E3%83%83%E3%83%89%E3%82%B7%E3%83%BC%E3%83%88%E3%81%AB%E5%8F%96%E5%BE%97%E3%81%97%E3%81%9F%E3%81%84

*/

function main() {

  // n件区切りでレコードを取得(100～500で指定可能)
  const GET_RECORD_MAX_LIMIT = 100

  // シート名
  const MAIN_SHEET_NAME = "メイン"
  const MASTER_SHEET_NAME = "master"

  // メイン_行位置
  const HEADER_ROW = 1                    //1行目が項目

  // master_行位置
  const APP_INFOMATION_ROW = 3

  // mastet_列位置
  const API_TOKEN_COLUMN= 1               //A列「apiToken」
  const APP_ID_COLUMN= 2                  //B列「appID」
  const DOMAIN_COLUMN = 3                 //C列「ドメイン」
  const QUERY_NAME_COLUMN = 4             //D列「一覧名称」
  const QUERY_COLUMN = 5                  //E列「一覧のクエリー」

  // シートを取得
  main_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MAIN_SHEET_NAME);
  master_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MASTER_SHEET_NAME);

  // マスターシートから情報取得
  const API_TOKEN = master_sheet.getRange(APP_INFOMATION_ROW, API_TOKEN_COLUMN).getValue()
  const APP_ID = master_sheet.getRange(APP_INFOMATION_ROW, APP_ID_COLUMN).getValue()
  const DOMAIN = master_sheet.getRange(APP_INFOMATION_ROW, DOMAIN_COLUMN).getValue()
  const QUERY_NAME = master_sheet.getRange(APP_INFOMATION_ROW, QUERY_NAME_COLUMN).getValue()
  const QUERY = master_sheet.getRange(APP_INFOMATION_ROW, QUERY_COLUMN).getValue()

  // 実行確認
  let is_run_popup = Browser.msgBox("「" + QUERY_NAME + "」一覧にて転記を開始しますか？", Browser.Buttons.OK_CANCEL);
  if(is_run_popup == "cancel"){
    Browser.msgBox("中止しました。");
    return;
  }

  // 数式開始列より前の列に対し、2行目以降の値削除
  let clear_row = main_sheet.getRange(HEADER_ROW + 1, 1,main_sheet.getMaxRows(),main_sheet.getLastColumn())
  clear_row.clear();  //値、書式削除

  // スプシに即反映
  SpreadsheetApp.flush()

  // クエリーをエンコード化(全角スペース込み)
  const VIEW_QUERY = encodeURIComponent(QUERY + ' ')

  // レコードの一括取得に必要なURL生成  
  const URL_GET_RECORDS = "https://"+ DOMAIN + ".cybozu.com/k/v1/records.json";

  // スプシの項目名を1次元配列で取得
  let header_array = get_row_array(HEADER_ROW)
  
  // 取得するフィールドコードを設定
  let param_fields = "&fields=$id," + encodeURIComponent(header_array.join(","));

  // 取得件数初期化
  let ofs = 0

  // ループ回数カウント(100件毎に1加算)
  let loop_count = 0  

  // 取得した全レコードを格納する配列
  let output_all_record = []

  // スプレッドシート右下にトースト表示(3秒表示)
  progress("実行開始", "kintoneレコードの転記を開始します…" , 3)

  // レコード取得ループ(100件毎)
  // 例)0～100件目、101～200件目、201～300件目
  while (true){

    // クエリ条件設定(100件毎に繰り返し取得する)
    let param_query = "&query=" + VIEW_QUERY + " limit " + GET_RECORD_MAX_LIMIT + " offset " + ofs;
    let url = URL_GET_RECORDS + "?app=" + APP_ID + param_fields + param_query;

    // 100件取得
    let res = JSON.parse(UrlFetchApp.fetch(url, {"method": "get","headers": {"X-Cybozu-API-Token": API_TOKEN}}));

    // レコード取得件数
    let record_length = res["records"].length

    // 100件毎にループした回数をカウント
    loop_count = loop_count + 1

    // ループ初回でレコードを1件も取得出来ない場合は異常終了
    if(loop_count === 1 && record_length === 0){

      // 異常終了メッセージ
      Browser.msgBox("1件も取得出来ませんでした。\\n実際のアプリ一覧でも1件も存在していない事を確認して下さい。");
      return
    
    // ループを複数回行い、1件も取得出来なくなった(全件取得完了)場合は処理終了
    }else if(record_length === 0){
      break
    }

    // スプレッドシート右下にトースト表示(3秒表示)
    progress("進行状況", (ofs + 1) + "～" + (loop_count * GET_RECORD_MAX_LIMIT) + "件目：取得中…" , 3)

    // 1レコード分の取得情報ループ
    res["records"].forEach(function(record){

      // 1レコード分を格納する配列を初期化
      let row = [];

      // スプシの項目名ループ
      for(i=0; i < header_array.length; i++){

        // 1レコード格納用配列に格納
        row.push(record[header_array[i]].value);

      }
      
      // 全レコード格納配列に、取得した1レコード分を格納
      output_all_record.push(row);

    });

    // 取得件数更新
    ofs += GET_RECORD_MAX_LIMIT

  };

  // スプレッドシートに取得した全レコードを反映
  main_sheet.getRange(HEADER_ROW + 1 , 1, output_all_record.length, output_all_record[0].length).setValues(output_all_record);

  // スプシに即反映
  SpreadsheetApp.flush()

  // スプレッドシート右下にトースト表示(3秒表示)
  progress("実行終了", "kintoneレコードの転記が完了しました。", 3)

  // メッセージボックスでも完了表示
  Browser.msgBox("kintoneレコードの転記が完了しました。")
  
  // 処理終了
  return 

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

概要：指定クエリーで取得出来た全レコードをスプレッドシートに反映

参考URL：

・【公式】レコードの一括取得（クエリで条件を指定）

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-

・kintoneのデータをスプレッドシートに取得する

https://specially198.com/get-kintone-data-into-a-spreadsheet/

・レコード一覧を指定したkintoneのデータを、スプレッドシートに取得したい

https://developer.cybozu.io/hc/ja/community/posts/6021383965849-%E3%83%AC%E3%82%B3%E3%83%BC%E3%83%89%E4%B8%80%E8%A6%A7%E3%82%92%E6%8C%87%E5%AE%9A%E3%81%97%E3%81%9Fkintone%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92-%E3%82%B9%E3%83%97%E3%83%AC%E3%83%83%E3%83%89%E3%82%B7%E3%83%BC%E3%83%88%E3%81%AB%E5%8F%96%E5%BE%97%E3%81%97%E3%81%9F%E3%81%84

function main() {

// n件区切りでレコードを取得(100～500で指定可能)

const GET_RECORD_MAX_LIMIT = 100

// シート名

const MAIN_SHEET_NAME = "メイン"

const MASTER_SHEET_NAME = "master"

// メイン_行位置

const HEADER_ROW = 1 //1行目が項目

// master_行位置

const APP_INFOMATION_ROW = 3

// mastet_列位置

const API_TOKEN_COLUMN= 1 //A列「apiToken」

const APP_ID_COLUMN= 2 //B列「appID」

const DOMAIN_COLUMN = 3 //C列「ドメイン」

const QUERY_NAME_COLUMN = 4 //D列「一覧名称」

const QUERY_COLUMN = 5 //E列「一覧のクエリー」

// シートを取得

main_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MAIN_SHEET_NAME);

master_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MASTER_SHEET_NAME);

// マスターシートから情報取得

const API_TOKEN = master_sheet.getRange(APP_INFOMATION_ROW, API_TOKEN_COLUMN).getValue()

const APP_ID = master_sheet.getRange(APP_INFOMATION_ROW, APP_ID_COLUMN).getValue()

const DOMAIN = master_sheet.getRange(APP_INFOMATION_ROW, DOMAIN_COLUMN).getValue()

const QUERY_NAME = master_sheet.getRange(APP_INFOMATION_ROW, QUERY_NAME_COLUMN).getValue()

const QUERY = master_sheet.getRange(APP_INFOMATION_ROW, QUERY_COLUMN).getValue()

// 実行確認

let is_run_popup = Browser.msgBox("「" + QUERY_NAME + "」一覧にて転記を開始しますか？", Browser.Buttons.OK_CANCEL);

if(is_run_popup == "cancel"){

Browser.msgBox("中止しました。");

return;

}

// 数式開始列より前の列に対し、2行目以降の値削除

let clear_row = main_sheet.getRange(HEADER_ROW + 1, 1,main_sheet.getMaxRows(),main_sheet.getLastColumn())

clear_row.clear(); //値、書式削除

// スプシに即反映

SpreadsheetApp.flush()

// クエリーをエンコード化(全角スペース込み)

const VIEW_QUERY = encodeURIComponent(QUERY + ' ')

// レコードの一括取得に必要なURL生成

const URL_GET_RECORDS = "https://"+ DOMAIN + ".cybozu.com/k/v1/records.json";

// スプシの項目名を1次元配列で取得

let header_array = get_row_array(HEADER_ROW)

// 取得するフィールドコードを設定

let param_fields = "&fields=$id," + encodeURIComponent(header_array.join(","));

// 取得件数初期化

let ofs = 0

// ループ回数カウント(100件毎に1加算)

let loop_count = 0

// 取得した全レコードを格納する配列

let output_all_record = []

// スプレッドシート右下にトースト表示(3秒表示)

progress("実行開始", "kintoneレコードの転記を開始します…" , 3)

// レコード取得ループ(100件毎)

// 例)0～100件目、101～200件目、201～300件目

while (true){

// クエリ条件設定(100件毎に繰り返し取得する)

let param_query = "&query=" + VIEW_QUERY + " limit " + GET_RECORD_MAX_LIMIT + " offset " + ofs;

let url = URL_GET_RECORDS + "?app=" + APP_ID + param_fields + param_query;

// 100件取得

let res = JSON.parse(UrlFetchApp.fetch(url, {"method": "get","headers": {"X-Cybozu-API-Token": API_TOKEN}}));

// レコード取得件数

let record_length = res["records"].length

// 100件毎にループした回数をカウント

loop_count = loop_count + 1

// ループ初回でレコードを1件も取得出来ない場合は異常終了

if(loop_count === 1 && record_length === 0){

// 異常終了メッセージ

Browser.msgBox("1件も取得出来ませんでした。\\n実際のアプリ一覧でも1件も存在していない事を確認して下さい。");

return

// ループを複数回行い、1件も取得出来なくなった(全件取得完了)場合は処理終了

}else if(record_length === 0){

break

}

// スプレッドシート右下にトースト表示(3秒表示)

progress("進行状況", (ofs + 1) + "～" + (loop_count * GET_RECORD_MAX_LIMIT) + "件目：取得中…" , 3)

// 1レコード分の取得情報ループ

res["records"].forEach(function(record){

// 1レコード分を格納する配列を初期化

let row = [];

// スプシの項目名ループ

for(i=0; i < header_array.length; i++){

// 1レコード格納用配列に格納

row.push(record[header_array[i]].value);

}

// 全レコード格納配列に、取得した1レコード分を格納

output_all_record.push(row);

});

// 取得件数更新

ofs += GET_RECORD_MAX_LIMIT

};

// スプレッドシートに取得した全レコードを反映

main_sheet.getRange(HEADER_ROW + 1 , 1, output_all_record.length, output_all_record[0].length).setValues(output_all_record);

// スプシに即反映

SpreadsheetApp.flush()

// スプレッドシート右下にトースト表示(3秒表示)

progress("実行終了", "kintoneレコードの転記が完了しました。", 3)

// メッセージボックスでも完了表示

Browser.msgBox("kintoneレコードの転記が完了しました。")

// 処理終了

return

}

// 概要：指定1行分の1次元配列取得
function get_row_array(row) {

  // シートを取得
  let sheet = SpreadsheetApp.getActiveSheet();

  //指定行を2次元配列で取得
  // 例)項目名
  let row_array = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues();

  //2次元配列を1次元配列に変更
  row_array = Array.prototype.concat.apply([], row_array);
 
  return row_array
 
}

// 参考：https://qiita.com/YasumiYasumi/items/3b71d8ebc1997be9104f
// スプレッドシート右下にトースト(通知)を表示
// 備忘録：何回も表示させると処理が重くなる
function progress(title, msg, display_seconds){

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // ログにも同じメッセージを表示
  console.log(msg)

  // トースト表示
  ss.toast(msg, title, display_seconds);
  Utilities.sleep(1500);

}

// 概要：指定1行分の1次元配列取得

function get_row_array(row) {

// シートを取得

let sheet = SpreadsheetApp.getActiveSheet();

//指定行を2次元配列で取得

// 例)項目名

let row_array = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues();

//2次元配列を1次元配列に変更

row_array = Array.prototype.concat.apply([], row_array);

return row_array

}

// 参考：https://qiita.com/YasumiYasumi/items/3b71d8ebc1997be9104f

// スプレッドシート右下にトースト(通知)を表示

// 備忘録：何回も表示させると処理が重くなる

function progress(title, msg, display_seconds){

var ss = SpreadsheetApp.getActiveSpreadsheet();

// ログにも同じメッセージを表示

console.log(msg)

// トースト表示

ss.toast(msg, title, display_seconds);

Utilities.sleep(1500);

}

ソースコード解説

本スプレッドシートを他アプリや、他一覧でも使いまわす場合は、下記1～4を変える必要があります。

：「VIEW_QUERY」
masterシートの、
下記オレンジ枠D列「一覧名称」をユーザーが好きに変更し、E列「一覧のクエリー」をGAS側に渡すという処理になっております。

(私含む)クエリーの書き方良く分からない…という方は、
下記手順で簡単にクエリーを取得出来ます！

実際に設定してあるkintoneアプリ内一覧を開く
F12キーを押して開発者画面を表示する
「Console」タブをクリックする
「kintone.app.getQuery();」を貼り付けて「Enter」で実行する

：「URL_GET_RECORDS」
下記黄色線箇所のドメイン名を指定する
「https://”+ ドメイン + “.cybozu.com/k/v1/records.json」

※画像はサンプルです

：「APP_ID」
下記黄色線箇所のアプリ番号を指定する

※画像はサンプルです

：「param_fields」
「fields=$id,」の後ろに
エンコードした「取得したいフィールド達(カンマ区切り)」を結合する

// 取得するフィールドコードを設定
let param_fields = "&fields=$id," + encodeURIComponent("フィールドコードA,フィールドコードB,フィールドコードC")

1 2	// 取得するフィールドコードを設定 let param_fields = "&fields=$id," + encodeURIComponent("フィールドコードA,フィールドコードB,フィールドコードC")