Google Sheets: Download Choice Data
Download Choice Data in specified 2 columns from Google Sheet.
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.`;
}
});
}

Download

2021-03-10 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/googlesheets-getidslabels/
The Addon-import feature is available with Professional or Enterprise edition.

Notes

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

Capture

See also

%d bloggers like this: