3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

Sheet.js+kintone REST APIでExcelの内容をkintoneに流し込む

Last updated at Posted at 2022-03-25

背景

 「Excel上にあるデータをkintoneに登録したいがフォームから手動で登録するのは面倒。ただkintoneの標準機能ではExcelファイルのデータ流し込み機能はない。どうしよう」という状況に陥りました。そこでSheet.jsとkintone APIを駆使してExcel上のデータをkintoneに自動で登録してくれる機能を実装しました。

前提

 ・以下のような構造データを流し込みたい
  image.png
  「名前」+「出身」→ kintoneのテーブルパーツに対応
  「メモ」→ kintoneの単体フォームパーツに対応
image.png

注意事項

 ・カラム名(上でいう"名前", "出身", "メモ")はExcelのシート上でユニークな値であること
 ・Z列までにデータを配置しておくこと(AA列以降は×)
 ・上記を満たしていればセル群の場所はどこでもよい
  (上記のExcelを以下のように変えても問題ないです)
image.png

手順

①アプリの作成
 今回は以下のフォームを設置
 ・添付ファイルフォーム(Excelファイルアップ用)
  (フィールドコード:"添付ファイル")
 ・テーブル化した文字列(1行)フォーム(名前、出身の入力用)
  (テーブルのフィールドコード:"テーブル")
  (フォームのフィールドコード:それぞれ"名前", "出身")→Excelのカラム名と同一にする
 ・文字列(複数行)フォーム(メモの入力用)
  (フィールドコード:"メモ")→Excelのカラム名と同一にする

②パッケージの準備
 「Javascript / CSSでカスタマイズ」ページにて、以下をURL指定で追加する
  https://unpkg.com/xlsx/dist/xlsx.full.min.js
  https://unpkg.com/@kintone/rest-api-client@2.0.7/umd/KintoneRestAPIClient.js

③プラグインの準備
 「プラグイン」ページにてJSEdit for kintoneを追加
 (※事前にkintoneシステム管理から利用可能なプラグインとして登録する必要あり)
④JSEdit for kintoneより以下コードを保存する

excel_autocompletion.js
// 設定値
//テーブル周り
const TABLE_FIELD_CODE = 'テーブル';
const TARGET_FIELD_CODE_1 = '名前';
const TARGET_FIELD_CODE_2 = '出身';
const TARGET_ROWS_NUM = 3;
//単体フォーム周り
const TARGET_FIELD_CODE_3 = 'メモ';
//添付ファイルパーツ
const FILE_FIELD_CODE = '添付ファイル';


(function() {
 "use strict";

  kintone.events.on(['app.record.create.submit.success'], async function(event) {
    if(event.record[FILE_FIELD_CODE].value[0]){
      var client = new KintoneRestAPIClient();
      // 添付ファイルを読み込む
      var data = await client.file.downloadFile({
        fileKey: event.record[FILE_FIELD_CODE].value[0].fileKey,
      });
      // 添付ファイルデータ
      var workbook = XLSX.read(data, { type: 'array' });
      var sheet = workbook.Sheets[workbook.SheetNames[0]];
      
      //複数行データの取得
      var data_1 = extract_excel_data(sheet, TARGET_FIELD_CODE_1, TARGET_ROWS_NUM);
      var data_2 = extract_excel_data(sheet, TARGET_FIELD_CODE_2, TARGET_ROWS_NUM);
      //1行データの取得(ここは敢えて↑と異なり、次のセルの値を取得する方法でロジック作成)
      var column_cell_address_3 = get_column_cell_address(sheet, TARGET_FIELD_CODE_3);
      var data_3 = sheet[next_cell_address(column_cell_address_3)].v;

      //↑で作成したdata_1, data_2, data_3を用いてPUT API用のパラメータを作成
      //参考:https://developer.cybozu.io/hc/ja/articles/200752984-%E3%83%AC%E3%82%B3%E3%83%BC%E3%83%89%E6%9B%B4%E6%96%B0%E3%81%AB%E3%81%8A%E3%81%91%E3%82%8B%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E6%93%8D%E4%BD%9C%E3%81%AE%E3%83%86%E3%82%AF%E3%83%8B%E3%83%83%E3%82%AF
      var params = ... 割愛 
      
      kintone.api(kintone.api.url('/k/v1/record.json', true), 'PUT', params, function(resp) {
        // success
        console.log(resp);
      }, function(error) {
        // error
        console.log(error);
      });
      
    } else {
      return event;
    }
    
  });
  
  //Excelのシートから対象カラムのセルアドレスを抜き出す
  function get_column_cell_address(sheet, column_name){
    var address = '';
    Object.entries(sheet).filter(function(value){
      if(value[1].v == column_name){
        address = value[0];
      }
    });
    return address;
  }
  
  //セルの列記号を返す
  function get_column_letter(cell_address){
    return cell_address.charAt();
  }
  
  //セルの行番号を返す
  function get_row_number(cell_address){
    return Number(cell_address.replace(/[^0-9]/g, ''));
  }
  
  //指定セルの下のセルアドレスを返す
  function next_cell_address(cell_address){
    var column_letter = get_column_letter(cell_address);
    var row_number = get_row_number(cell_address);
    return column_letter + (row_number + 1);
  }
  
  //Excelからデータ群を抽出し、配列で返す
  function extract_excel_data(sheet, column_name, rows_num){
    var column_cell_address = get_column_cell_address(sheet, column_name);
    var column_letter = get_column_letter(column_cell_address);
      //取り込みたいデータの1番目の行
    var start_cell_num = get_row_number(next_cell_address(column_cell_address));
    var array = [];
      
      //定義した行分を対象とする
    for(var i = start_cell_num; i < start_cell_num + rows_num; i++){
      var cell_num = column_letter + i;
      var data = sheet[cell_num] ? {'value': sheet[cell_num].v} : {'value': ''};
      array.push(data);
    }
    return array;
  }
  
})();

⑤新規作成画面よりExcelファイルを添付し、保存する
 以下、詳細画面。無事保存完了。
image.png

感想

 標準機能としてあったら便利なのにね…

参考

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?