Google スプレッドシート: 選択肢データの一括取得

// OAuth2 config
// Authorization Endpoint URL: https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
// Token Endpoint URL: https://accounts.google.com/o/oauth2/token
// Scope: https://www.googleapis.com/auth/spreadsheets.readonly
// Consumer Key: (Get by Google Developers Console)
// Consumer Secret: (Get by Google Developers Console)
main();
function main(){
  //// == 工程コンフィグの参照 / Config Retrieving ==
  const oauth = configs.get( "conf_OAuth2" );
  const docId = configs.get( "conf_DataIdB" );
  const sheet = configs.get( "conf_DataIdC" );
  const rangeI = configs.get( "conf_DataIdD" );
  const rangeL = configs.get( "conf_DataIdE" );
  const idDataDef = configs.getObject( "conf_DataIdF" );
  const labelDataDef = configs.getObject( "conf_DataIdG" );
  
  //// == 演算 / Calculating ==
  const {idList, labelList} = getChoice( oauth, docId, sheet, rangeI, rangeL );

  //// == ワークフローデータへの代入 / Data Updating ==
  engine.setData( idDataDef, idList.join("\n") );
  engine.setData( labelDataDef, labelList.join("\n") );
}

/**
 * Google スプレッドシートの選択肢データを取得
 * @param {String} oauth OAuth2 認証設定
 * @param {String} docId スプレッドシートの ID
 * @param {String} sheet シート名
 * @param {String} rangeI 選択肢 ID の領域
 * @param {String} rangeL 選択肢 ラベル の領域
 * @return {Object} obj 選択肢データを格納したオブジェクト
 * @return {Array<String>} obj.idList 選択肢 ID を格納した配列
 * @return {Array<String>} obj.labelList 選択肢ラベルを格納した配列
 */
function getChoice( oauth, docId, sheet, rangeI, rangeL ) {
  // Method: spreadsheets.values.batchGet
  const uri = `https://sheets.googleapis.com/v4/spreadsheets/${docId}/values/:batchGet`;
  const response = httpClient.begin()
    .authSetting( oauth )
    .queryParam( "ranges", `${sheet}!${rangeI}` )
    .queryParam( "ranges", `${sheet}!${rangeL}` )
    .queryParam( "valueRenderOption", "UNFORMATTED_VALUE" )
    .queryParam( "majorDimension", "COLUMNS" )
    .get( uri );
  const status = response.getStatusCode();
  const responseStr = response.getResponseAsString();
  if (status !== 200) {
    const accessLog = `---GET request--- ${status}\n${responseStr}`;
    engine.log( accessLog );
    throw `Failed to get. status:${status}`;
  }
  const jsonObj = JSON.parse( responseStr );

  const I_LABEL = "Choice-ID";
  const L_LABEL = "Label";
  const idCols = jsonObj.valueRanges[0].values;
  const labelCols = jsonObj.valueRanges[1].values;
  // error for empty column, multiple columns
  checkCols(idCols, I_LABEL);
  checkCols(labelCols, L_LABEL);

  const idList = idCols[0];
  const labelList = labelCols[0];
  // error for different number of cells
  if (idList.length !== labelList.length) {
    throw "Number of cells in two ranges is different.";
  }
  if (idList.length > 150000) {
    throw "Number of Choice Data is over 150,000.";
  }
  // error for an empty cells, over 1000 characters
  checkData(idList, I_LABEL);
  checkData(labelList, L_LABEL);
  // error for same ids
  const idSet = new Set( idList ); // idList の重複確認用の Set
  if ( idSet.size !== idList.length ) {
    throw `Same ${I_LABEL}s are in range.`;
  }

  return {idList, labelList};
}

/**
 * 列を確認し、空(undefined)や複数列ある場合はエラー
 * @param {Array} cols 列データの配列
 * @param {String} label エラー用のラベル
 */
function checkCols(cols, label) {
  if (cols === undefined) {
    throw `No Data in ${label}s range.`;
  }
  if (cols.length > 1) {
    throw `Multiple Columns in ${label}s range.`;
  }
}

/**
 * 配列の要素を確認し、空文字や1000字以上のデータがあればエラー
 * @param {Array<String>} list データが格納された配列
 * @param {String} label エラー用のラベル
 */
function checkData(list, label) {
  list.forEach(str => {
    if (str === "") {
      throw `Empty Cell is in ${label}s range.`;
    }
    if (str.length > 1000) {
      throw `Over 1000-character ${label} is in range.`;
    }
  });
}