
Google Sheets: Append New Rows (Table type data)
Google スプレッドシート: 行追加 (テーブル型データ)
This item appends values of a Table type data item at the last of the sheet.
Basic Configs
- Step Name
- Note
Configs for this Auto Step
- conf_OAuth2
- C1: OAuth2 Config *
- conf_DataIdW
- C2: Target Spreadsheet ID *
- conf_DataIdX
- C3: Target Sheet Title *
- conf_DataIdT
- C4: Table type data item *
- conf_DataIdA
- C-A: Field Name of Sub Data Item for Column-A
- conf_DataIdB
- C-B: Field Name of Sub Data Item for Column-B
- conf_DataIdC
- C-C: Field Name of Sub Data Item for Column-C
- conf_DataIdD
- C-D: Field Name of Sub Data Item for Column-D
- conf_DataIdE
- C-E: Field Name of Sub Data Item for Column-E
- conf_DataIdF
- C-F: Field Name of Sub Data Item for Column-F
- conf_DataIdG
- C-G: Field Name of Sub Data Item for Column-G
- conf_DataIdH
- C-H: Field Name of Sub Data Item for Column-H
- conf_DataIdI
- C-I: Field Name of Sub Data Item for Column-I
- conf_DataIdJ
- C-J: Field Name of Sub Data Item for Column-J
Notes
- Spreadsheet ID is contained in the URL. https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
Capture

See also
Script (click to open)
- An XML file that contains the code below is available to download
- google-sheets-row-append-by-table.xml (C) Questetra, Inc. (MIT License)
- If you are using Professional, you can modify the contents of this file and use it as your own add-on auto step
// 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}`;
}
}