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)

main();

function main() {
    const tableDataDef = configs.getObject("conf_DataIdT");
    const oauth2 = configs.get( "conf_OAuth2" );
    const spreadsheetId = retrieveStringData("conf_DataIdW", "Target Spreadsheet ID");
    const sheetName = retrieveStringData("conf_DataIdX", "Target Sheet Title");
    const fieldNames = getFieldNames();

    // 追加するデータを準備
    const rows = makeRows(tableDataDef, fieldNames);

    // シート名からシート ID の取得
    const sheetId = getSheetId(oauth2, spreadsheetId, sheetName);

    // データの追加
    appendData(oauth2, sheetId, spreadsheetId, rows);
}

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

/**
 * スプレッドシートの各列に追加するサブデータ項目のフィールド名一覧を取得
 * @return {*[]}
 */
function getFieldNames() {
    const CONF_FIELD_NAMES = [
        'conf_DataIdA',
        'conf_DataIdB',
        'conf_DataIdC',
        'conf_DataIdD',
        'conf_DataIdE',
        'conf_DataIdF',
        'conf_DataIdG',
        'conf_DataIdH',
        'conf_DataIdI',
        'conf_DataIdJ',
    ];

    const fieldNames = CONF_FIELD_NAMES.map(conf => configs.get(conf));

    if (fieldNames.every(fieldName => fieldName === null || fieldName === '')) {
        // 全項目、設定値が空なら、エラー
        throw "No Data to add is selected.";
    }
    return fieldNames;
}

/**
 * シート名からシート ID の取得
 * @param {String} oauth2
 * @param {String} spreadsheetId
 * @param {String} sheetName
 * @return {Number}
 */
function getSheetId(oauth2, spreadsheetId, sheetName) {
    const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}?includeGridData=false`;
    const response = httpClient.begin()
        .authSetting(oauth2)
        .get(uri);
    const status = response.getStatusCode();
    const responseJson = response.getResponseAsString();
    if (status !== 200) {
        engine.log(responseJson);
        throw `Can't get sheet information. status: ${status}`;
    }
    const jsonObj = JSON.parse(responseJson);
    engine.log(`SPREADSHEET (TITLE: ${jsonObj.properties.title}) has ${jsonObj.sheets.length} sheets`);
    for (let i = 0; i < jsonObj.sheets.length; i++) {
        if (jsonObj.sheets[i].properties.title === sheetName) {
            const sheetId = parseInt(jsonObj.sheets[i].properties.sheetId, 10);
            engine.log(`SHEET TITLE: ${sheetName} exists (id: ${sheetId})`);
            return sheetId;
        }
    }
    // error (not exist)
    throw `Sheet ${sheetName} does not exist`;
}

/**
 * 追加用の行データを作成
 * @param {ProcessDataDefinitionView} tableDataDef
 * @param {Array<String>} fieldNames
 * @return {Array<Object>}
 */
function makeRows(tableDataDef, fieldNames) {
    // テーブル型データおよびサブデータ項目の定義を取得
    const tableData = engine.findData(tableDataDef);
    if (tableData === null) {
        throw "the table data is empty.";
    }
    const subDataDefs = tableDataDef.getSubDataDefinitions();

    // subDataDef のマップを作成。キーはフィールド名。
    const subDataDefMap = makeSubDataDefMap(subDataDefs);

    //行データの作成
    const rowCount = tableData.size();
    const rows = new Array(rowCount);
    for (let i = 0; i < rowCount; i++) {
        const values = makeRowValues(i, tableData, subDataDefMap, fieldNames);
        rows[i] = {values};
    }

    return rows;
}

/**
 * SubDataDefinitionView の Map を作成
 * @param {Array<SubDataDefinitionView>} subDataDefs
 * @return {Map<string, SubDataDefinitionView>}
 */
function makeSubDataDefMap(subDataDefs) {
    const subDataDefMap = new Map();
    for (let i = 0; i < subDataDefs.length; i++) {
        const def = subDataDefs[i];
        const varName = def.getVarName();
        if (varName !== null) {
            subDataDefMap.set(varName, def);
        }
    }
    return subDataDefMap;
}

/**
 * AppendData 用、各行の値を作成する
 * @param rowNumber
 * @param {ScriptListArray} tableData
 * @param {Map<String, SubDataDefinitionView>>} subDataDefMap
 * @param {Array<String>} fieldName
 * @return {Array<Object>}
 */
function makeRowValues(rowNumber, tableData, subDataDefMap, fieldNames) {
    const values = new Array(fieldNames.length);
    let flag = true;
    // 後ろの列から前の列へ
    for (let j = fieldNames.length - 1; j >= 0; j--) {
        const fieldName = fieldNames[j];
        const cellValue = getCellValue(rowNumber, tableData, subDataDefMap, fieldName);
        if (flag && cellValue === '') {
            // 後ろの列は、空である限り null を指定
            values[j] = null;
        } else {
            // 基本、stringValue
            let valueType = 'stringValue';
            if (cellValue !== '') {
                if (subDataDefMap.get(fieldName).matchDataType('DECIMAL')) {
                    //空ではなく、数値型データの場合のみ、numberValue
                    valueType = 'numberValue';
                }
            }
            values[j] = {
                "userEnteredValue": {
                    [valueType]: cellValue
                }
            };
            flag = false;
        }
    }
    if (flag) {
        throw "There is an empty row in data to append.";
    }
    return values;
}

/**
 * セルの値の取得
 * @param {Number} rowNumber
 * @param {ScriptListArray} tableData
 * @param {Map<String, SubDataDefinitionView>} subDataDefMap
 * @param {String} fieldName
 * @return {String}
 */
function getCellValue(rowNumber, tableData, subDataDefMap, fieldName) {
    if (fieldName === "" || fieldName === null) {
        // サブデータ項目の指定が無い場合
        return '';
    }
    if (!subDataDefMap.has(fieldName)) {
        //when selected column doesn't exist
        throw `Field Name ${fieldName} does not exist`;
    }
    const cellValue = tableData.get(rowNumber, fieldName);
    if (cellValue.length > 50000) {
        //when the number of letters in text is over 50000
        throw "Can't set text over 50,000 character.";
    }
    return cellValue;
}

/**
 * データを Google Sheets に送信する
 * @param {String} oauth2
 * @param {String} sheetId
 * @param {String} spreadsheetId
 * @param {Array<Object>} rows
 */
function appendData(oauth2, sheetId, spreadsheetId, rows) {
    const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}:batchUpdate`;
    const obj = {
        requests: [{
            appendCells: {
                rows,
                sheetId,
                fields: "*"
            }
        }]
    };
    const response = httpClient.begin()
        .authSetting(oauth2)
        .body(JSON.stringify(obj), "application/json")
        .post(uri);
    const status = response.getStatusCode();
    if (status !== 200) {
        engine.log(response.getResponseAsString());
        throw `Failed to append data. status: ${status}`;
    }
}