
Google Sheets: Append New Row
This item adds a row at the last of the sheet, and fills each cell of that row with data.
Basic Configs
- Step Name
- Note
Configs for this Auto Step
- conf_OAuth2
- C1: OAuth2 Setting *
- conf_DataIdW
- C2: Target Spreadsheet ID *
- conf_DataIdX
- C3: Target Sheet Title *
- conf_RowNum
- C4: Data item to save the appended row number
- conf_DataIdA
- C-A: Column-A Value of New Row#{EL}
- conf_DataIdB
- C-B: Column-B Value of New Row#{EL}
- conf_DataIdC
- C-C: Column-C Value of New Row#{EL}
- conf_DataIdD
- C-D: Column-D Value of New Row#{EL}
- conf_DataIdE
- C-E: Column-E Value of New Row#{EL}
- conf_DataIdF
- C-F: Column-F Value of New Row#{EL}
- conf_DataIdG
- C-G: Column-G Value of New Row#{EL}
- conf_DataIdH
- C-H: Column-H Value of New Row#{EL}
- conf_DataIdI
- C-I: Column-I Value of New Row#{EL}
- conf_DataIdJ
- C-J: Column-J Value of New Row#{EL}
Notes
- Spreadsheet ID is contained in the URL. https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
- If you want to add after the 11th column (K column)
- use “Google Sheets: Update Row” together
- Specify and update after “k column” of [Added row]
- replace with “Google Sheets: Sheet, Append TSV“(Add-on Auto-Steps)
- use “Google Sheets: Update Row” together
Capture

See also
Script (click to open)
- An XML file that contains the code below is available to download
- google-sheets-row-append.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 sample at [OAuth 2.0 Setting]
// - 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(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const oauth2 = configs.get( "conf_OAuth2" );
const spreadsheetId = retrieveStringData( "conf_DataIdW", "Target Spreadsheet ID" );
const sheetName = retrieveStringData( "conf_DataIdX", "Target Sheet Title" );
const rowNumDef = configs.getObject( "conf_RowNum" );
const columns = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"];
const dataArray = columns.map( i => configs.get( `conf_DataId${i}` ) );
//// == Calculating / 演算 ==
const row = buildRowObj(dataArray);
const sheetId = getSheetId(oauth2, spreadsheetId, sheetName); // Access to the API 1st(Get GID)
appendRow(oauth2, spreadsheetId, sheetId, row); // Access to the API 2nd(POST)
if(rowNumDef === null) return;
// only when rowNumDef is not null
const rowNum = getLastRowNum(oauth2, spreadsheetId, sheetName); // Access to the API 3rd(GET)
//// == ワークフローデータへの代入 / Data Updating ==
engine.setData(rowNumDef, rowNum);
}
/**
* 文字列データを 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;
}
/**
* 文字列データを所定の形式のセルオブジェクトに変換する。
* @param {String} data 文字列データ
* @return {Object} cellObj 文字列データが格納されたセルオブジェクト
*/
function buildCellObj( data ){
const cellObj = {};
cellObj.userEnteredValue = {};
if( data === "" || data === null){
cellObj.userEnteredValue.stringValue = "";
return cellObj;
}else{ // for "STRING"
if(data.length > 50000){
throw "Can't set text over 50,000 characters.";
}
cellObj.userEnteredValue.stringValue = data;
return cellObj;
}
}
/**
* 文字列データを所定の形式の行オブジェクトに変換する。
* @param {Array<String>} dataArray 文字列データの配列
* @return {Object} rowObj 文字列データが格納された行オブジェクト
*/
function buildRowObj( dataArray ){
const rowObj = {};
rowObj.values = dataArray.map( data => buildCellObj( data ) );
// 後ろから空白の要素数を数える
let count = 0;
for(let i = rowObj.values.length; i > 0; i--) {
if(rowObj.values[i-1].userEnteredValue.stringValue === ""){
count++;
}else{ // 空白でなければ、ループから抜ける
break;
}
}
// 空白部分を削除
if(count > 0){
rowObj.values = rowObj.values.slice(0, -count);
}
// すべてが空白ならエラー
if(rowObj.values.length === 0){
throw "No Data to add is set.";
}
return rowObj;
}
/**
* GET リクエストを送信し、シート ID (GID) を取得する。
* @param {String} oauth2 OAuth2 設定名
* @param {String} spreadsheetId スプレッドシート ID
* @param {String} sheetName シート名
* @return {Number} sheetId シート ID (GID)
*/
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 responseStr = response.getResponseAsString();
if (status !== 200) {
engine.log(responseStr);
throw `Failed to get sheet information. status: ${status}`;
}
const jsonObj = JSON.parse(responseStr);
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 {Number} sheetId シート ID (GID)
* @return {Object} row 行オブジェクト
*/
function buildRequestObj(sheetId, row){
const requestObj = {};
requestObj.requests = [];
requestObj.requests[0] = {};
requestObj.requests[0].appendCells = {};
requestObj.requests[0].appendCells.sheetId = sheetId;
requestObj.requests[0].appendCells.fields = "*";
requestObj.requests[0].appendCells.rows = [];
requestObj.requests[0].appendCells.rows[0] = row;
return requestObj;
}
/**
* 行追加の POST リクエストを送信する。
* @param {String} oauth2 OAuth2 設定名
* @param {String} spreadsheetId スプレッドシート ID
* @param {Number} sheetId シート ID (GID)
* @param {Object} row 行オブジェクト
*/
function appendRow(oauth2, spreadsheetId, sheetId, row){
const apiUri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}:batchUpdate`;
const requestObj = buildRequestObj(sheetId, row);
const response = httpClient.begin()
.authSetting( oauth2 )
.body( JSON.stringify( requestObj ), "application/json" )
.post( apiUri ); // HttpResponseWrapper
const httpStatus = response.getStatusCode();
if( httpStatus !== 200 ){
engine.log(response.getResponseAsString());
throw `Failed to append data. status: ${httpStatus}`;
}
}
/**
* GET リクエストを送信し、データが入力されている最終行の行番号を返す。
* @param {String} oauth2 OAuth2 設定名
* @param {String} spreadsheetId スプレッドシート ID
* @param {String} sheetName シート名
* @return {String} データが入力されている最終行の行番号
*/
function getLastRowNum(oauth2, spreadsheetId, sheetName){
const apiUri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadsheetId)}/values/${encodeURIComponent(sheetName)}`;
const response = httpClient.begin()
.authSetting( oauth2 )
.queryParam( "majorDimension", "ROWS" )
.get( apiUri ); // HttpResponseWrapper
const httpStatus = response.getStatusCode();
const responseStr = response.getResponseAsString();
if( httpStatus !== 200 ){
engine.log(responseStr);
throw `Failed to get rows in the sheet. status: ${httpStatus}`;
}
const jsonObj = JSON.parse(responseStr);
return jsonObj.values.length.toString();
}