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
// Consumer Key: (Get by Google Developers Console)
// Consumer Secret: (Get by Google Developers Console)

const GOOGLE_API_URI = 'https://sheets.googleapis.com/v4/';

const CELL_NUM = 4; // 扱えるセルの数
const VALUE_MAX_LENGTH = 50000; // セルの値の最大文字数

const main = () => {
    //// == 工程コンフィグ・ワークフローデータの参照 / Config & Data Retrieving ==
    const oauth2 = configs.getObject('conf_OAuth2');
    const sheetId = retrieveStringData( "conf_SheetId", "Spreadsheet ID" );
    const sheetTitle = retrieveStringData( "conf_SheetTitle", "Spreadsheet Title" );

    let valueInputOption = configs.get('conf_ValueInputOption');
    if (valueInputOption === '') {
        valueInputOption = 'RAW';
    }
    const {cellsToUpdate, valuesToUpdate} = retrieveCellsAndValuesToUpdate();
    const {cellsToGet, valueDefs} = retrieveCellsToGetAndValueDefs();

    //// == 演算・ワークフローデータへの代入 / Calculating & Data Updating ==
    if (cellsToUpdate.length === 0 && cellsToGet.length === 0) {
        throw new Error('No cells to update or get.');
    }
    if (cellsToUpdate.length > 0) {
        updateCells(oauth2, sheetId, sheetTitle, valueInputOption, cellsToUpdate, valuesToUpdate);
    }
    if (cellsToGet.length > 0) {
        const values = getCells(oauth2, sheetId, sheetTitle, cellsToGet);
        valueDefs.forEach((def, i) => {
            engine.setData(def, values[i]);
        });
    }
};

/**
 * 文字列データを 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;
}

/**
  * セル指定のバリデーション
  * @param {String} cell  セル指定の文字列
  * @param {String} label  エラー出力用ラベル
  */
const validateCell = (cell, label) => {
    const regExp = new RegExp('^[A-Z]+[1-9][0-9]*$');
    if (!regExp.test(cell)) {
        throw new Error(`${label} is invalid.`);
    }
};

/**
 * 工程コンフィグから更新するセルと更新後の値を取得する
 * セルが指定されておらず、値だけ指定されている組がある場合はエラー
 * @returns {Object} result
 * @returns {Array<String>} result.cellsToUpdate  更新するセル
 * @returns {Array<String>} result.valuesToUpdate  更新後の値
 */
const retrieveCellsAndValuesToUpdate = () => {
    const cellsToUpdate = [];
    const valuesToUpdate = [];
    for (let i = 0; i < CELL_NUM; i++) {
        const cellToUpdate = configs.get(`conf_CellToUpdate${i + 1}`);
        const valueToUpdate = configs.get(`conf_ValueToUpdate${i + 1}`);
        if (cellToUpdate === '') {
            if (valueToUpdate !== '') {
                throw new Error(`Cell ${i + 1} to update is empty while its new value is specified.`);
            }
            // セルも値も指定されていない場合はスキップ
            continue;
        }
        validateCell(cellToUpdate, `Cell ${i + 1} to update`);
        if (valueToUpdate.length > VALUE_MAX_LENGTH) {
            throw new Error(`New value for Cell ${i + 1} exceeds ${VALUE_MAX_LENGTH} characters.`);
        }
        cellsToUpdate.push(cellToUpdate);
        valuesToUpdate.push(valueToUpdate);
    }
    return {cellsToUpdate, valuesToUpdate};
};

/**
 * 工程コンフィグから取得するセルと保存先データ項目を取得する
 * セルと保存先データ項目の片方だけ指定されている組がある場合はエラー
 * @returns {Object} result
 * @returns {Array<String>} result.cellsToGet  取得するセル
 * @returns {Array<DataDefinitionView>} result.valueDefs  値を保存するデータ項目
 */
const retrieveCellsToGetAndValueDefs = () => {
    const cellsToGet = [];
    const valueDefs = [];
    for (let i = 0; i < CELL_NUM; i++) {
        const cellToGet = configs.get(`conf_CellToGet${i + 1}`);
        const valueDef = configs.getObject(`conf_ValueDef${i + 1}`);
        if (cellToGet === '' && valueDef === null) {
            continue;
        } else if (cellToGet !== '' && valueDef !== null) {
            validateCell(cellToGet, `Cell ${i + 1} to get`);
            cellsToGet.push(cellToGet);
            valueDefs.push(valueDef);
        } else {
            throw new Error(`Cell ${i + 1} to get and data item to save its value must be specified at the same time.`);
        }
    }
    return {cellsToGet, valueDefs};
};

/**
 * セルを更新する
 * @param {AuthSettingWrapper} oauth2  OAuth2 設定情報
 * @param {String} sheetId  スプレッドシートの ID
 * @param {String} sheetTitle  シートタイトル
 * @param {String} valueInputOption  セル更新時の値の解釈方法
 * @param {Array<String>} cellsToUpdate  更新するセル
 * @param {Array<String>} valuesToUpdate  更新後の値
 */
const updateCells = (oauth2, sheetId, sheetTitle, valueInputOption, cellsToUpdate, valuesToUpdate) => {
    // Method: spreadsheets.values.batchUpdate
    const url = `${GOOGLE_API_URI}spreadsheets/${encodeURIComponent(sheetId)}/values:batchUpdate`;
    const jsonBody = buildBatchUpdateBody(sheetTitle, valueInputOption, cellsToUpdate, valuesToUpdate);
    const response = httpClient.begin()
        .authSetting(oauth2)
        .body(JSON.stringify(jsonBody), 'application/json')
        .post(url);
    const status = response.getStatusCode();
    const responseStr = response.getResponseAsString();
    if (status !== 200) {
        engine.log(responseStr);
        throw new Error(`Failed to update cells. status: ${status}`);
    }
};

/**
 * セル更新のリクエストボディを作成する
 * @param {String} sheetTitle  シートタイトル
 * @param {String} valueInputOption  セル更新時の値の解釈方法
 * @param {Array<String>} cellsToUpdate  更新するセル
 * @param {Array<String>} valuesToUpdate  更新後の値
 * @returns {Object} requestBody  リクエストボディ
 */
const buildBatchUpdateBody = (sheetTitle, valueInputOption, cellsToUpdate, valuesToUpdate) => {
    const data = [];
    cellsToUpdate.forEach((cell, i) => {
        data.push({
            'range': `${sheetTitle}!${cell}`,
            'values': [[valuesToUpdate[i]]]
        });
    });
    return {
        'valueInputOption': valueInputOption,
        data
    };
};

/**
 * セルを取得する
 * @param {AuthSettingWrapper} oauth2  OAuth2 設定情報
 * @param {String} sheetId  スプレッドシートの ID
 * @param {String} sheetTitle  シートタイトル
 * @param {Array<String>} cellsToGet  取得するセル
 * @returns {Array<String>} values  取得した値
 */
const getCells = (oauth2, sheetId, sheetTitle, cellsToGet) => {
    // Method: spreadsheets.values.batchGet
    const url = `${GOOGLE_API_URI}spreadsheets/${encodeURIComponent(sheetId)}/values:batchGet`;
    let request = httpClient.begin()
        .authSetting(oauth2)
        .queryParam('valueRenderOption', 'UNFORMATTED_VALUE')
        .queryParam('dateTimeRenderOption', 'FORMATTED_STRING');
    cellsToGet.forEach((cell) => {
        request = request.queryParam('ranges', `${sheetTitle}!${cell}`);
    });
    const response = request.get(url);
    const status = response.getStatusCode();
    const responseStr = response.getResponseAsString();
    if (status !== 200) {
        engine.log(responseStr);
        throw new Error(`Failed to get cells. status: ${status}`);
    }
    return JSON.parse(responseStr).valueRanges.map((valueRange) => {
        const values = valueRange.values;
        if (values === undefined) {
            return '';
        }
        return valueRange.values[0][0].toString();
    });
};