Zenn
🍊

Pleasanter x Google 連携:スプレッドシートからデータを書き込む(削除編)

2022/12/10に公開
Google Sheets
pleasanter
tech

ゴール

  • Google SpreadSheet で編集した内容を Pleasanter に適用する

  • 力及ばず、今回は削除のみ対応

    • チェックボックスを上手くだせなかったので、削除列には 1 などを入れる想定

  • 読み取りについての前の記事を下敷にしています(コード.gs の部分)

コード

  • 使いかたとして main を実行して、一旦シートに取り込まれている状態で、削除列に 1 とかくとデータが消されます。
  • スプレッドシートのデータは消していません。
    • ここでは、最新のデータは、都度取り出す想定のためです。

コード.gs

読み取りの記事からの変更点として、

  1. 削除列を追加
  2. post 関数で、インプット用パラメータを受けられるようにした(今後の新規・編集を見据えての対応)
const apikey = ''; //apikey を記入します
const host = 'https://demo.pleasanter.org';
const siteId = 123456; //サイト ID を記入します

const main = () => {
  writeToSheet(post(host, 'records', 'get', siteId));
};

/**
 * エンドポイントを決定するためのヘルパ
 * @param control:string // records データレコード, site サイト, users ユーザ, depts 組織, groups グループ
 * @param act:string // get delete update create
 * @param id:number // (レコードおよびサイトを操作するとき)親の id
 * */
const controlDefiner = (control, act, id) => {
  const controlName = normalizeControl(control, act, id);
  const actionName = normalizeAction(control, act);
  if (controlName === '' || actionName === '')
    throw new Error(`${control} ${act} is not implemented`);
  return { controller: controlName, action: actionName };
};
const normalizeControl = (control, act, id) => {
  switch (control) {
    case 'records':
    case 'record':
    case 'site':
    case 'table':
      if (act[0] === 'u' || act[0] === 'd') {
        return 'items';
      }
      return `items/${id}`;
    case 'users':
    case 'user':
      return 'users';
    case 'depts':
    case 'dept':
      return 'depts';
    case 'groups':
    case 'group':
      return 'groups;';
    default:
      return '';
  }
};
const normalizeAction = (target, action) => {
  switch (target) {
    case 'site':
    case 'table':
      return `${action}site`;
    case 'records':
    case 'record':
    case 'users':
    case 'user':
    case 'depts':
    case 'dept':
    case 'groups':
    case 'group':
      return action;
    default:
      return '';
  }
};
const urlBuilder = (host, control, act, id) => {
  const { controller, action } = controlDefiner(control, act, id);
  switch (action[0]) {
    case 'u':
    case 'd':
      return `${host}/api/${controller}/${id}/${action}`;
    case 'c':
    case 'g':
    default:
      return `${host}/api/${controller}/${action}`;
  }
};
//

// データを取得。ページンングなし
const post = (host, control, act, id, body = {}) => {
  const param = {
    contentType: 'application/json',
    method: 'post',
    payload: JSON.stringify({ ...body, ApiVersion: 1.1, ApiKey: apikey }),
    muteHttpExceptions: true,
  };
  const url = urlBuilder(host, control, act, id);
  const res = UrlFetchApp.fetch(url, param);
  if (res.getResponseCode() !== 200) return [];
  return JSON.parse(res.getContentText()).Response?.Data;
};
// 階層化しているデータ項目を展開する。添付は除外
const flatten = (data, parent = {}) =>
  Object.entries(data).reduce(
    (a, c) =>
      c[0].startsWith('Attachments')
        ? a
        : c[0].endsWith('Hash')
        ? flatten(c[1], a)
        : { ...a, [c[0]]: c[1] },
    parent,
  );
// データ項目からヘッダ行を作る
const headerFromFirst = (data) => Object.keys(data[0]);
const objectToArray = (data, appendCheck = '削除') => {
  const header =
    appendCheck === undefined
      ? headerFromFirst(data)
      : [appendCheck].concat(headerFromFirst(data));
  return [header].concat(data.map((r) => header.map((c) => r[c])));
};

// データをシートに書き込む
const writeToSheet = (data, sheetName = 'シート1') => {
  const sheet =
    sheetName === undefined
      ? SpreadsheetApp.getActiveSheet()
      : SpreadsheetApp.getActive().getSheetByName(sheetName);
  if (sheet === undefined || sheet === null) {
    console.error(`no sheet ${sheetName}`);
    return;
  }
  if (data.length < 1) return;
  sheet.clearContents();
  const rows = data.map(flatten);
  const values = objectToArray(rows);
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
};

OnEdit.gs

  • OnEdit をトリガーとしてインストールします
    • シンプルトリガーだと fetch ができないためです
const onEdit = (e) => {
  if (e.authMode.toString() === 'LIMITED') return;
  const sheet = e.range.getSheet();
  const data = sheet.getDataRange().getValues();
  const header = data[0];
  const idColName = header.includes('ResultId') ? 'ResultId' : 'IssueId';
  const idCol = header.findIndex((e) => e === idColName);
  const colNums = range(e.range.getColumn() - 1, e.range.getWidth());
  if (!colNums.includes(idCol)) {
    colNums.push(idCol);
  }
  const rowNums = range(e.range.getRow() - 1, e.range.getHeight());
  if (!rowNums.includes(0)) {
    rowNums.push(0);
  }
  const edited = data
    .filter((_, i) => rowNums.includes(i))
    .map((r) => r.filter((_, i) => colNums.includes(i)));
  handler(edited, idColName);
};
const handler = (data, idColName) => {
  const idCol = data[0].findIndex((e) => e === idColName);
  handleDelete(filterDelete(data, idCol));
};
const filterDelete = (data, idCol) => {
  const idxDel = data[0].findIndex((e) => e === '削除');
  if (idxDel === -1) return [];
  return data.slice(1).map((e) => e[idCol]);
};
const handleDelete = (ids) => {
  ids.forEach((e) => post(host, 'records', 'delete', e));
};
const range = (start, len) => Array.from({ length: len }, (_, i) => start + i);

さいごに

ひとまず、今回はこれまで。
引き続き、更新と新規作成を対応予定です。

GitHubで編集を提案

Discussion