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)
function main() {
    //// == 工程コンフィグの参照 / Config Retrieving ==
    const oauth = configs.getObject("conf_OAuth2");
    const docId = retrieveStringData( "conf_DataIdB", "Spreadsheet ID" );
    const sheet = retrieveStringData( "conf_DataIdC", "Spreadsheet Title" );
    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"));
}

/**
 * 文字列データを config から読み出す。空であればエラー。
 * @param  {String} confName config 名
 * @param  {String} label エラーメッセージ用のラベル
 * @return {String} string 文字列データ
 */
function retrieveStringData( confName, label ){
    const dataDef = configs.getObject( confName );
    let string;
    if (dataDef !== null) {
        string = engine.findData(dataDef);
    } else {
        string = configs.get(confName);
    }
    if (string === null || string === "") {
        throw new Error(`${label} is empty.`);
    }
    return string;
}

/**
 * Google スプレッドシートの選択肢データを取得
 * @param {AuthSettingWrapper} oauth2  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("dateTimeRenderOption", "FORMATTED_STRING")
        .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 new Error(`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 new Error("Number of cells in two ranges is different.");
    }
    if (idList.length > 150000) {
        throw new Error("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 new Error(`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 new Error(`No Data in ${label}s range.`);
    }
    if (cols.length > 1) {
        throw new Error(`Multiple Columns in ${label}s range.`);
    }
}

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