Google Sheets: Append New Row
Adds new cells after the last row with data in a sheet, inserting new rows into the sheet if necessary.
Configs
  • C1: OAuth2 Setting Name *
  • C2: Target Spreadsheet ID *
  • C3: Target Sheet Title *
  • C-A: Column-A Value of New Row#{EL}
  • C-B: Column-B Value of New Row#{EL}
  • C-C: Column-C Value of New Row#{EL}
  • C-D: Column-D Value of New Row#{EL}
  • C-E: Column-E Value of New Row#{EL}
  • C-F: Column-F Value of New Row#{EL}
  • C-G: Column-G Value of New Row#{EL}
  • C-H: Column-H Value of New Row#{EL}
  • C-I: Column-I Value of New Row#{EL}
  • C-J: Column-J Value of New Row#{EL}
Script (click to open)

// 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)


//// == Config Retrieving / 工程コンフィグの参照 ==
main();
function main(){
const oauth2 = configs.get( "conf_OAuth2" );
const spreadsheetId = configs.get( "conf_DataIdW" );
const sheetName = configs.get( "conf_DataIdX" );
const dataIdA = configs.get( "conf_DataIdA" );
const dataIdB = configs.get( "conf_DataIdB" );
const dataIdC = configs.get( "conf_DataIdC" );
const dataIdD = configs.get( "conf_DataIdD" );
const dataIdE = configs.get( "conf_DataIdE" );
const dataIdF = configs.get( "conf_DataIdF" );
const dataIdG = configs.get( "conf_DataIdG" );
const dataIdH = configs.get( "conf_DataIdH" );
const dataIdI = configs.get( "conf_DataIdI" );
const dataIdJ = configs.get( "conf_DataIdJ" );
// convert 'java.lang.String' to 'javascript string'

//// == Calculating / 演算 ==
// Request QUERY (?a=b)
// (no set)
let requestObj = {};
requestObj.requests = [];
requestObj.requests[0] = {};
requestObj.requests[0].appendCells = {};
requestObj.requests[0].appendCells.sheetId = -1;
requestObj.requests[0].appendCells.fields = "*";
requestObj.requests[0].appendCells.rows = [];
requestObj.requests[0].appendCells.rows[0] = {};
requestObj.requests[0].appendCells.rows[0].values = [];

requestObj.requests[0].appendCells.rows[0].values[0] = cellDataObject( dataIdA );
requestObj.requests[0].appendCells.rows[0].values[1] = cellDataObject( dataIdB );
requestObj.requests[0].appendCells.rows[0].values[2] = cellDataObject( dataIdC );
requestObj.requests[0].appendCells.rows[0].values[3] = cellDataObject( dataIdD );
requestObj.requests[0].appendCells.rows[0].values[4] = cellDataObject( dataIdE );
requestObj.requests[0].appendCells.rows[0].values[5] = cellDataObject( dataIdF );
requestObj.requests[0].appendCells.rows[0].values[6] = cellDataObject( dataIdG );
requestObj.requests[0].appendCells.rows[0].values[7] = cellDataObject( dataIdH );
requestObj.requests[0].appendCells.rows[0].values[8] = cellDataObject( dataIdI );
requestObj.requests[0].appendCells.rows[0].values[9] = cellDataObject( dataIdJ );

let count = 0;
let flag = 1;
for(let i = 9; i > -1; i--){
if(requestObj.requests[0].appendCells.rows[0].values[i].userEnteredValue.stringValue === ""){
count++;
if(flag){
requestObj.requests[0].appendCells.rows[0].values[i] = null;
}
}else{
flag = 0;
}
}
if(count === 10){
throw "No Data to add is set.";
}

// Access to the API 1st(Get GID)
const getgid = httpClient.begin().authSetting( oauth2 )
.get (`https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}`);
const status = getgid.getStatusCode();
if (status !== 200) {
engine.log(getgid.getResponseAsString());
throw `Can't get sheet information. status: ${status}`;
}
const getRes = JSON.parse(getgid.getResponseAsString());
const sheetArray = getRes.sheets;
for (var i = 0;i < sheetArray.length;i++){
if(sheetArray[i].properties.title === sheetName){
requestObj.requests[0].appendCells.sheetId = sheetArray[i].properties.sheetId;
}
}
if (requestObj.requests[0].appendCells.sheetId === -1){
throw `Sheet '${sheetName}' doesn't exist`;
}

const apiUri = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}:batchUpdate`;
// Access to the API 2nd(POST)
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}`;
}

}
// Request BODY (JSON, Form Parameters, etc)
/**
* 文字列データを所定の形式のオブジェクトに変換する。
* @param {String} data 文字列データ
* @return {Object} dataObj 文字列データが格納されたオブジェクト
*/
function cellDataObject( data ){
let dataObj = {};
dataObj.userEnteredValue = {};
if( data === "" || data === null){
dataObj.userEnteredValue.stringValue = "";
return dataObj;
}else{ // for "STRING"
if(data.length > 50000){
throw "Can't set text over 50,000 character.";
}
dataObj.userEnteredValue.stringValue = data;
return dataObj;
}
}


Download

2021-02-03 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/googlesheets-appendcells/
The Addon-import feature is available with Professional or Enterprise edition.

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: