// GraalJS Script (engine type: 2)
/*
NOTES
- An error will occur, if the number of Tab codes on each line is not the same.
- Skips lines with only Line Break (blank lines)
- Numerical String is evaluated with "parseFloat()" after removing the comma.
- Nothing is added to the cell that failed to be evaluated.
- Date String is evaluated with "Date()"
- Nothing is added to the cell that failed to be evaluated.
- new Date('December 17, 1995 03:24:00')
- new Date('1995-12-17T03:24:00')
- new Date('1995-12-17 03:24')
- new Date('1995-12-17')
- In case of "YYYY-MM-DD", "T00:00:00" will be added (interpreted as Local Time)
- Other date-only forms are interpreted as a UTC time.
- TSV文字列の Tab コードの数が全行同数でない場合、エラーとなります
- 改行のみの行(空行)については、当該行をスキップして読み込みます
- 数値文字列は、カンマ(桁区切り文字)が除去された上で、"parseFloat()" 関数で評価されます
- 評価に失敗したセルは、何も追記されません
- 日時文字列は、"Date()" 関数で評価されます
- 評価に失敗したセルは、何も追記されません
- new Date('December 17, 1995 03:24:00')
- new Date('1995-12-17T03:24:00')
- new Date('1995-12-17 03:24')
- new Date('1995-12-17')
- なお "YYYY-MM-DD" 書式の場合 "T00:00:00" が自動付加(ローカルタイム化)されます
- その他の「日付のみの形式」は UTC 時刻と解釈されます
*/
//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const strOauthSetting = configs.get( "OAuth2ConfA" ); // required
const strTsv = configs.get( "StrConfB" ) + ""; // required
const strColIds = configs.get( "StrConfC" ) + ""; // required
const strDocId = configs.get( "StrConfD" ) + ""; // required
let strSheetId = configs.get( "StrConfE" ) + "";
let strSheetName = configs.get( "StrConfE2") + "";
if( strTsv === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {B TSV} not specified \n" );
}
if( strColIds === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {C Column IDs} not specified \n" );
}
if( strDocId === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {D Spreadsheet ID} not specified \n" );
}
if( strSheetId === "" ){
engine.log( " AutomatedTask ConfigWarning:" +
" Config {E Sheet Id} (recommended) is empty" );
if( strSheetName === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {E Sheet (both Id and Name)} not specified \n" );
}
}
//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)
//// == Calculating / 演算 ==
/// check TSV Dataset
const arrTsv = strTsv.split("\n");
let arrTsvSpliced = [];
for( let i = 0; i < arrTsv.length; i++ ){ // Remove Blank Lines
if( arrTsv[i] !== ""){
arrTsvSpliced.push( arrTsv[i] );
}else{
engine.log( " AutomatedTask StringWarning: TSV LineId '[" + i +
"]': empty (skipped as dataset)" );
}
}
let arrTsvCells = [];
for( let i = 0; i < arrTsvSpliced.length; i++ ){
arrTsvCells[i] = [];
let arrTmp = arrTsvSpliced[i].split("\t");
for( let j = 0; j < arrTmp.length; j++ ){
arrTsvCells[i].push( arrTmp[j] );
}
if( i !== 0 ){
if( arrTsvCells[0].length !== arrTsvCells[i].length ){
throw new Error( "\n AutomatedTask UnexpectedTsvError:" +
" Dataset TSV not matrix \n" );
}
}
}
engine.log( " AutomatedTask TSV Loaded: Number of Rows (Lines) " + arrTsvSpliced.length );
engine.log( " AutomatedTask TSV Loaded: Number of Columns " + arrTsvCells[0].length );
const arrColIds = strColIds.split(",");
engine.log( " AutomatedTask Column Ids to be inserted: " +
strColIds + " (" + arrColIds.length + ")" );
/// check Spreadsheet (SheetName to SheetId)
const token = httpClient.getOAuth2Token( strOauthSetting );
if( strSheetId === "" ){
// prepare request1
// ref) https://developers.google.com
// /sheets/api/reference/rest/v4/spreadsheets/get
let uri1 = "https://sheets.googleapis.com/v4/spreadsheets/" +
strDocId;
let request1 = httpClient.begin(); // HttpRequestWrapper
request1 = request1.bearer( token );
engine.log( " AutomatedTask ApiRequest1 Prepared" );
// post request1
const response1 = request1.get( uri1 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + uri1 );
const response1Code = response1.getStatusCode() + ""; // (primitive string)
const response1Body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
response1Code + "\n" + response1Body + "\n" );
}
// parse response1
/* engine.log( response1Body ); // debug
response sample
{
"spreadsheetId": "17rwXXXXxS34yyyyYr-KzzzzX0_iWWWWOfHdxxxxRCLM",
"properties": { … },
"sheets": [
{
"properties": {
"sheetId": 0,
"title": "sheet1",
"index": 0,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 100,
"columnCount": 20,
"frozenRowCount": 1
}
}
}
],
"spreadsheetUrl": "https://docs.google.com/spreadsheets/d/17rw......OfHdxxxxRCLM/edit"
}
*/
const response1Obj = JSON.parse( response1Body );
for( let i = 0; i < response1Obj.sheets.length; i++ ){
engine.log( " - " + response1Obj.sheets[i].properties.title );
if( response1Obj.sheets[i].properties.title === strSheetName ){
strSheetId = response1Obj.sheets[i].properties.sheetId;
engine.log( " AutomatedTask SheetName '" + strSheetName +
"': SheetId " + strSheetId );
break;
}
}
if( strSheetId === "" ){
throw new Error( "\n AutomatedTask ConfigError:" +
" Config {E2 Sheet Name} does not exist \n" );
}
}// end_of: if( strSheetId === "" )
/// append Dataset to Sheet
// prepare request2
// ref) https://developers.google.com
// /sheets/api/reference/rest/v4/spreadsheets/request#appendcellsrequest
let request2Obj = {};
request2Obj.requests = [];
request2Obj.requests[0] = {};
request2Obj.requests[0].appendCells = {};
request2Obj.requests[0].appendCells.rows = [];
request2Obj.requests[0].appendCells.sheetId = strSheetId;
request2Obj.requests[0].appendCells.fields = "*";
const regBpmsYMD = /^\d{4}-\d{2}-\d{2}$/;
for( let i = 0; i < arrTsvCells.length; i++ ){
request2Obj.requests[0].appendCells.rows[i] = {};
request2Obj.requests[0].appendCells.rows[i].values = [];
for( let j = 0; j < arrColIds.length; j++ ){
request2Obj.requests[0].appendCells.rows[i].values[j] = {};
if( arrColIds[j].slice(-1) === "n" ){ // Numeric Type
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue.numberValue =
parseFloat( arrTsvCells[i][parseInt( arrColIds[j] )].replace(/,/g, "") );
}else if( arrColIds[j].slice(-1) === "d" ){ // Date Type in Sheet
let strDatetimeTmp = arrTsvCells[i][parseInt( arrColIds[j] )];
if( regBpmsYMD.test( strDatetimeTmp ) ){
strDatetimeTmp += "T00:00:00";
}
let dateTmp = new Date( strDatetimeTmp );
let numSerial = dateTmp.getTime() / 86400000 + 25569;
// - https://developers.google.com/sheets/api/reference/rest/v4/DateTimeRenderOption
// 25569 means 1970-01-01 00:00:00, 86400 means the number of seconds per day
numSerial += engine.getTimeZoneOffsetInMinutes() / (60 * 24); // WorkflowPlatform TimeZone
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredFormat = {};
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredFormat.numberFormat = {};
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredFormat.numberFormat.type = "DATE";
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue.numberValue =
numSerial;
}else{
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue = {};
request2Obj.requests[0].appendCells.rows[i].values[j].userEnteredValue.stringValue =
arrTsvCells[i][parseInt( arrColIds[j] )];
}
}
}
let uri2 = "https://sheets.googleapis.com/v4/spreadsheets/" +
strDocId + ":batchUpdate";
let request2 = httpClient.begin(); // HttpRequestWrapper
request2 = request2.bearer( token );
request2 = request2.body( JSON.stringify( request2Obj ), "application/json" );
engine.log( " AutomatedTask ApiRequest2 Prepared" );
// post request2
const response2 = request2.post( uri2 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest2 Start: " + uri2 );
const response2Code = response2.getStatusCode() + ""; // (primitive string)
const response2Body = response2.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse2 Status: " + response2Code );
if( response2Code !== "200"){
throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
response2Code + "\n" + response2Body + "\n" );
}
// parse response2
// (nothing)
/* engine.log( response2Body ); // debug
response sample
{
"spreadsheetId": "1rviXXXXXHcRYYYYjFN1ZZZZfM6EWWWWqifaVVVVzgSM",
"replies": [
{}
]
}
*/
//// == Data Updating / ワークフローデータへの代入 ==
// (nothing)
} //////// END "main()" /////////////////////////////////////////////////////////////////
/*
APPENDIX
- "SpreadsheetID" and "SheetID" in URL
- https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=SHEETID
- To Get "ClientId" and "Secret"
- Access to https://console.developers.google.com/apis/credentials
- This request requires authorization with at least one of the following scopes:
- https://www.googleapis.com/auth/drive
- https://www.googleapis.com/auth/drive.file
- https://www.googleapis.com/auth/spreadsheets
- OAuth Setting sample
- "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
- Error Log sample (Tab codes not constant)
- AutomatedTask UnexpectedTsvError: Dataset TSV not matrix
*/
Pingback: New Record to Google Sheets (TSV) – Questetra Support
Pingback: Google Sheets: Sheet Row, Update with Singleline TSV – Questetra Support
Pingback: Multiline String, Dequeue First – Questetra Support
Pingback: Singleline TSV String, Extract Cell – Questetra Support
Pingback: Google Sheets: ValueRanges, Sum – Questetra Support
Pingback: Google Sheets: Two ValueRanges, Xlookup Reference – Questetra Support
Pingback: Wordpress.com: Media, List – Questetra Support
Pingback: Image-Charts: Bar Chart, Create – Questetra Support
Pingback: MoneyForward Journal, Create – Questetra Support