
Google スプレッドシート: 行追加 (Google Sheets: Append New Row)
シート末尾に1行追加し、その各セルにデータを入力します。必要あれば行領域を拡大します。
2019-06-20 (C) Questetra, Inc. (MIT License)
2019-06-20 (C) Questetra, Inc. (MIT License)
Configs
- C1: OAuth2 設定名 *
- C2: 入力先のスプレッドシートの ID *
- C3: 入力先のシートのタイトル *
- C-A: 末尾行の A 列に追加される値 #{EL}
- C-B: 末尾行の B 列に追加される値 #{EL}
- C-C: 末尾行の C 列に追加される値 #{EL}
- C-D: 末尾行の D 列に追加される値 #{EL}
- C-E: 末尾行の E 列に追加される値 #{EL}
- C-F: 末尾行の F 列に追加される値 #{EL}
- C-G: 末尾行の G 列に追加される値 #{EL}
- C-H: 末尾行の H 列に追加される値 #{EL}
- C-I: 末尾行の I 列に追加される値 #{EL}
- C-J: 末尾行の J 列に追加される値 #{EL}
Script
// Google Sheets Append Cells (ver. 20171031)
// (c) 2017, Questetra, Inc. (the MIT License)
// by spreadsheets.batchUpdate [AppendCellsRequest] (not by spreadsheets.values.append)
// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest
// 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.";
}
// Get OAuth2 Token
const token = httpClient.getOAuth2Token( oauth2 );
// Access to the API 1st(Get GID)
let getgid = httpClient.begin()
.bearer( token )
.get ("https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId);
const getRes = JSON.parse(getgid.getResponseAsString());
const sheetArray = getRes.sheets;
if(sheetArray == [] || sheetArray == null){
throw "Can't get sheet information";
}
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";
}
// Set Url (https://example.com/abc/def/)
const apiUri = "https://sheets.googleapis.com/v4/spreadsheets/"+ spreadsheetId + ":batchUpdate";
// Access to the API 2nd(POST)
let response = httpClient.begin()
.bearer( token )
.body( JSON.stringify( requestObj ), "application/json" )
.post( apiUri ); // HttpResponseWrapper
const httpStatus = response.getStatusCode() + "";
let accessLog = "---POST request--- " + httpStatus + "\n";
accessLog += response.getResponseAsString() + "\n";
if( response.getStatusCode() != 200 ){
throw accessLog;
}
//var responseObj = JSON.parse( response.getResponseAsString() );
// Retrieve Properties from Response-JSON
// (no action)
// Error Handling - https://stripe.com/docs/api#errors
// (no action)
//// == Output Log / ログ出力 ==
engine.log(accessLog);
}
// Request BODY (JSON, Form Parameters, etc)
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
Capture

Notes
- スプレッドシートの ID は、URL に含まれています。https://docs.google.com/spreadsheets/d/(sheet ID)/edit#gid=0