Google Sheets: Download Choice Data
Downloads Choice Data in 2 specified columns from a Google Sheet.
Configs: Common
  • Step Name
  • Note
Configs
  • C1: OAuth2 Setting *
  • C2: Source Spreadsheet ID *
  • C3: Source Sheet Title *
  • C4: Range of Choice IDs (e.g. “A1:A”) *
  • C5: Range of Choice Labels (e.g. “D2:D”) *
  • C6: Data item that will save Choice IDs *
  • C7: Data item that will save Choice Labels *
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

  • It is assumed that the number of values specified by C4 and C5 are the same, and there are no empty cells in the middle of the range.
  • The spreadsheet ID is contained in the URL. https://docs.google.com/spreadsheets/d/(Sheet ID)/edit#gid=0

Capture

See also

Appendix

  • google-sheets-choice-batchget.xml (C) Questetra, Inc. (MIT License)
    • If you are using Professional, you can modify the contents of this file and use it as your own add-on
%d bloggers like this: