Google Sheets 行追加 (TSV)

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