Google Sheets: Append New Rows (Table type data)
Appends values of table data item after the last row in a sheet. Inserts new rows if necessary.
2020-12-18 (C) Questetra, Inc. (MIT License)
Configs: Common
  • Step Name
  • Note
Configs
  • C1: OAuth2 Config *
  • C2: Target Spreadsheet ID *
  • C3: Target Sheet Title *
  • C4: Table type data item *
  • C-A: Field Name of Sub Data Item for Column-A
  • C-B: Field Name of Sub Data Item for Column-B
  • C-C: Field Name of Sub Data Item for Column-C
  • C-D: Field Name of Sub Data Item for Column-D
  • C-E: Field Name of Sub Data Item for Column-E
  • C-F: Field Name of Sub Data Item for Column-F
  • C-G: Field Name of Sub Data Item for Column-G
  • C-H: Field Name of Sub Data Item for Column-H
  • C-I: Field Name of Sub Data Item for Column-I
  • C-J: Field Name of Sub Data Item for Column-J
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
// Consumer Key: (Get by Google Developers Console)
// Consumer Secret: (Get by Google Developers Console)

main();

function main() {
    const tableDataDef = configs.getObject("conf_DataIdT");
    if (tableDataDef === null) {
        throw "Table Data is not specified.";
    }
    const oauth2 = getConfigValue("conf_OAuth2", 'OAuth2 Setting');
    const spreadsheetId = getConfigValue("conf_DataIdW", 'Spreadsheet ID');
    const sheetName = getConfigValue("conf_DataIdX", 'Sheet Name');
    const fieldNames = getFieldNames();

    // テーブル型データおよびサブデータ項目の定義を取得
    const tableData = engine.findData(tableDataDef);
    if (tableData === null) {
        throw "the table data is empty.";
    }
    const subDataDefs = tableDataDef.getSubDataDefinitions();

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

    // 追加するデータの追加
    const appendCells = makeAppendCells(tableData, sheetId, subDataDefs, fieldNames);
    //engine.log(JSON.stringify(appendCells));

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

/**
 * 設定値の取得。空ならエラー。
 * @param name
 * @param label エラーメッセージ用のラベル
 */
function getConfigValue(name, label) {
    const value = configs.get(name);
    if (value === '' || value === null) {
        throw `${label} is not specified.`;
    }
    return value;
}

/**
 * スプレッドシートの各列に追加するサブデータ項目の取得
 * @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/${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 {ScriptListArray} tableData
 * @param {Number} sheetId
 * @param {Array<SubDataDefinitionView>} subDataDefs
 * @param {Array<String>} fieldNames
 * @return {Object}
 */
function makeAppendCells(tableData, sheetId, subDataDefs, fieldNames) {
    // subDataDef のマップを作成。キーはフィールド名。
    const subDataDefMap = makeSubDataDefMap(subDataDefs);

    //行データの作成
    const rowCount = tableData.size();
    const rows = [rowCount];
    for (let i = 0; i < rowCount; i++) {
        const values = makeRowValues(i, tableData, subDataDefMap, fieldNames);
        rows[i] = {values};
        //engine.log(`${i}: ${JSON.stringify(rows[i])}`);
    }

    return {
        requests: [{
            appendCells: {
                rows,
                sheetId,
                fields: "*"
            }
        }]
    };
}

/**
 * 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 {[*]}
 */
function makeRowValues(rowNumber, tableData, subDataDefMap, fieldNames) {
    const values = [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 {Object}
 */
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} spreadsheetId
 * @param {Object} obj
 */
function appendData(oauth2, spreadsheetId, obj) {
    const uri = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}:batchUpdate`;

    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}`;
    }
}

Notes

Capture

See also

Appendix

%d bloggers like this: