
New Record to Google Sheets (TSV)
Adds the TSV data in String type data item to the end of a Google Spreadsheets and stores its communication log in the data item. If there exists no sheet, adds one automatically.
2018 (C) Questetra, Inc. (MIT License)
2018 (C) Questetra, Inc. (MIT License)
Configs
- A: Select TSV STRING DATA * *
- B: Set THROW COL IDs (e.g. “0,1,5d,3n,6”) * * #{EL}
- C: Set OAuth2 Config Name (at [OAuth 2.0 Setting]) * #{EL}
- D: Select STRING/SELECT for Spreadsheet ID (Non-existent Error) *
- E: Select STRING/SELECT for Sheet Name (Non-existent Create) *
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
Capture

Notes
- “0,1,5d, 3n, 6” means that appending ID: 0 as a String, ID: 1 as a String, ID: 5 as Date-time, ID: 3 as a number, ID: 6 as a String.
- The File ID can be obtained from the URL
See also
- 2016-11-07 Episode 508: Officialize Corporate Names in “Customer Master” by Corporate Number API
- 2016-09-20 Episode 501:You Don’t Know the Spending of Budget?