
Google Sheets 行追加 (TSV)
文字型データ項目に入力されている TSV データの内容を、Google Sheets の Sheet の末尾に追記し、通信ログをデータ項目に格納します。シートが無い場合には自動的にシートが追加されます
2018 © Questetra, Inc. (the MIT License)
https://support.questetra.com/ja/addons/googlesheets-appendtsv/
Configs
- A: TSV が格納されている文字列型データを選択してください *
- B: 送出データの項目 ID をセットしてください (例 “0,1,5d,3n,6”) *
- C: OAuth2通信許可設定名 (←[OAuth 2.0 設定]) *
- D: Spreadsheet ID が格納されている文字列型or選択肢型データを選択してください(存在しない場合、エラー) *
- E: Sheet 名が格納されている文字列型or選択肢型データを選択してください(存在しない場合、追加) *
Script
//// OAuth2 config sample
//// at Workflow App > Detail > OAuth2 Setting > (Get Refresh Token)
// - 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 from Google Developers Console)
// - Consumer Secret: (Get from Google Developers Console)
// Ref.
// - https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get
// - https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request
//////// START "main()" ////////
main();
function main(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const dataIdA = configs.get( "conf_DataIdA" ) + "";
const dataIdD = configs.get( "conf_DataIdD" ) + "";
const dataIdE = configs.get( "conf_DataIdE" ) + "";
const colIds = configs.get( "conf_ColIds" ) + "";
const oauth2 = configs.get( "conf_OAuth2" ) + "";
// convert 'java.lang.String' to javascript primitive 'string'
//// == Data Retrieving / ワークフローデータの参照 ==
let myTsv = engine.findDataByNumber( dataIdA );
if( myTsv === null){ throw new Error( "TSV no data" ); }else{ myTsv += "";}
let docId = "";
if( engine.findDataDefinitionByNumber( dataIdD ).matchDataType( "SELECT_SINGLE" ) ){
docId = engine.findDataByNumber( dataIdD ).get(0).getValue() + "";
}else{
docId = engine.findDataByNumber( dataIdD ) + "";
}
let sheetName = "";
if( engine.findDataDefinitionByNumber( dataIdE ).matchDataType( "SELECT_SINGLE" ) ){
sheetName = engine.findDataByNumber( dataIdE ).get(0).getValue() + "";
}else{
sheetName = engine.findDataByNumber( dataIdE ) + "";
}
//// == Calculating / 演算 ==
// obtain OAuth2 Access Token
const token = httpClient.getOAuth2Token( oauth2 );
/// (1) Sheet Exist Check
// prepare API Request
let apiRequest = httpClient.begin(); // HttpRequestWrapper
// - com.questetra.bpms.core.event.scripttask.HttpClientWrapper
apiRequest = apiRequest.bearer( token );
apiRequest = apiRequest.queryParam( "includeGridData", false );
// - To retrieve only the specific fields of the spreadsheet
// - Ref: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get
// access API (with post(), get(), put(), etc.)
const response = apiRequest.get( "https://sheets.googleapis.com/v4/spreadsheets/" + docId );
// - HttpResponseWrapper
const httpStatus = response.getStatusCode() + "";
const responseStr = response.getResponseAsString() + "";
const responseObj = JSON.parse( responseStr );
engine.log( "GET [spreadsheet specific]: " + httpStatus );
if( httpStatus !== "200" ){
let errorMessage = "\n- HTTP STATUS is not 200, Service Task stopped. -\n";
errorMessage += responseStr + "\n";
throw new Error( errorMessage );
}
// get Sheet ID
let sheetId = -1; // NotExist
engine.log( "SPREADSHEET TITLE: " + responseObj.properties.title );
for( let i = 0; i < responseObj.sheets.length; i++ ){
if( responseObj.sheets[i].properties.title === sheetName ){
sheetId = parseInt( responseObj.sheets[i].properties.sheetId, 10 );
engine.log( " SHEET NAME: " + sheetName + " exists, id=" + sheetId );
break;
}else{
engine.log( " SHEET NAME: " + sheetName + " does not exist" );
}
}
/// (2) Create New Sheet (if NotExist)
if(sheetId === -1){
// prepare API Request2
// - define Request2 Object for spreadsheets.batchUpdate request
sheetId = processInstance.getProcessInstanceId() - 0;
let requestObj2 = {};
requestObj2.requests = [];
requestObj2.requests[0] = {};
requestObj2.requests[0].addSheet = {};
requestObj2.requests[0].addSheet.properties = {};
requestObj2.requests[0].addSheet.properties.sheetId = sheetId;
requestObj2.requests[0].addSheet.properties.title = sheetName;
// - Ref: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#addsheetrequest
let apiRequest2 = httpClient.begin(); // HttpRequestWrapper
apiRequest2 = apiRequest2.bearer( token );
apiRequest2 = apiRequest2.body( JSON.stringify( requestObj2 ), "application/json" );
// access API (with post())
const response2 = apiRequest2.post( "https://sheets.googleapis.com/v4/spreadsheets/" + docId + ":batchUpdate" );
const httpStatus2 = response2.getStatusCode() + "";
const responseStr2 = response2.getResponseAsString() + "";
engine.log( "POST [AddSheet request]: " + httpStatus2 );
if( httpStatus2 !== "200" ){
let errorMessage2 = "\n- HTTP STATUS is not 200, Service Task stopped. -\n";
errorMessage2 += responseStr2 + "\n";
throw new Error( errorMessage2 );
}
}
/// (3) Append Records
// prepare API Request3
const arrColIds = colIds.split(",");
const arrMyTsv = myTsv.split("\n");
let requestObj3 = {};
requestObj3.requests = []; // Array
requestObj3.requests[0] = {};
requestObj3.requests[0].appendCells = {};
requestObj3.requests[0].appendCells.rows = []; // Array
requestObj3.requests[0].appendCells.sheetId = sheetId;
requestObj3.requests[0].appendCells.fields = "*";
for( let i = 0; i < arrMyTsv.length; i++ ){
requestObj3.requests[0].appendCells.rows[i] = {};
requestObj3.requests[0].appendCells.rows[i].values = [];
let arrMyCellValues = arrMyTsv[i].split("\t");
for( let j = 0; j < arrColIds.length; j++ ){
requestObj3.requests[0].appendCells.rows[i].values[j] = {};
if( arrColIds[j].slice(-1) === "n" ){ // Numeric Type
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredValue.numberValue = arrMyCellValues[parseInt( arrColIds[j] )];
}else if( arrColIds[j].slice(-1) === "d" ){ // Date Type
let dateMyCellDate = new Date( arrMyCellValues[parseInt( arrColIds[j] )] );
let serialNumber = dateMyCellDate.getTime() / 86400000 + 25569;
// - https://developers.google.com/sheets/api/reference/rest/v4/DateTimeRenderOption#ENUM_VALUES.SERIAL_NUMBER
// 25569 means 1970-01-01 00:00:00, 86400 means the number of seconds per day
serialNumber += engine.getTimeZoneOffsetInMinutes() / (60 * 24); // Depends on Your Drive Setting
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredFormat = {};
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredFormat.numberFormat = {};
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredFormat.numberFormat.type = "DATE";
// requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredFormat.numberFormat.type = "DATE_TIME";
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredValue.numberValue = serialNumber;
}else{
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
requestObj3.requests[0].appendCells.rows[i].values[j].userEnteredValue.stringValue = arrMyCellValues[parseInt( arrColIds[j] )];
}
}
}// - Ref: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest
let apiRequest3 = httpClient.begin(); // HttpRequestWrapper
apiRequest3 = apiRequest3.bearer( token );
apiRequest3 = apiRequest3.body( JSON.stringify( requestObj3 ), "application/json" );
// access API (with post())
const response3 = apiRequest3.post( "https://sheets.googleapis.com/v4/spreadsheets/" + docId + ":batchUpdate" );
const httpStatus3 = response3.getStatusCode() + "";
const responseStr3 = response3.getResponseAsString() + "";
engine.log( "POST [AppendCells request]: " + httpStatus3 );
if( httpStatus3 !== "200" ){
let errorMessage3 = "\n- HTTP STATUS is not 200, Service Task stopped. -\n";
errorMessage3 += responseStr3 + "\n";
throw new Error( errorMessage3 );
}
//// == Data Updating / ワークフローデータへの代入 ==
// (nothing)
} //////// END "main()" ////////
Download
GoogleSheets-appendTsv.xmlCapture

Notes
- “0,1,5d,3n,6” は、ID:0を文字列として、ID:1を文字列として、ID:5を日時として、ID:3を数字として、ID:6を文字列として追記することを意味します
- ドキュメント ID は URL 等を参照します
- https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
See also
- 2016-11-07 第508話:法人番号APIで「顧客マスター」を正式商号化
- 2016-09-20 第501話:予算の消費額がワカラン!?!