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

Download

Notes

  1. Spreadsheet ID is contained in the URL. https://docs.google.com/spreadsheets/d/(sheet ID)/edit#gid=0

Capture

See also

%d bloggers like this: