Google スプレッドシート: 選択肢データの一括取得 (Google Sheets: Download Choice Data)
Google スプレッドシートから指定した2列に入っている選択肢データを取得します。
Configs:共通設定
  • 工程名
  • メモ
Configs
  • C1: OAuth2 設定 *
  • C2: ダウンロード元のスプレッドシートの ID *
  • C3: ダウンロード元のシートのタイトル *
  • C4: 選択肢 ID の領域情報 (例 “A2:A”, “A:A”) *
  • C5: 選択肢ラベルの領域情報 (例 “B2:B”, “B:B”) *
  • C6: 選択肢 ID の一覧を保存するデータ項目 *
  • C7: 選択肢ラベルの一覧を保存するデータ項目 *
Script (click to open)


// 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.`;
}
});
}

Notes

  • C4 で指定した領域と C5 で指定した領域の数が同じであること、領域の途中に空セルが無いことが前提となります
  • スプレッドシートの ID は、URL に含まれています。https://docs.google.com/spreadsheets/d/(sheet ID)/edit#gid=0

Capture

See also

Download

  • google-sheets-choice-batchget.xml (C) Questetra, Inc. (MIT License)
    • Professional をご利用であればファイルの内容を改変することでオリジナルのアドオンとして活用できます
%d人のブロガーが「いいね」をつけました。