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