// GraalJS Script (engine type: 2)
/*
Notes:
- See URL for "Spreadsheet ID".
- https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
- When setting the SheetId rule, ensure that a unique positive integer is generated.
- If no rule, SheetId will be randomly generated by Google.
- Set as "#{processInstanceId}" to make "Process ID" SheetId.
- R2290: Dynamic value insertion by EL expression
- https://questetra.zendesk.com/hc/en-us/articles/360024293072-R2290
- If the SheetId already exists, the automated task ends with an error.
Notes(ja):
- スプレッドシートのIDはURLを参照してください。
- https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
- SheetId ルールを設定する場合は、ユニークな正の整数(int32)が生成されるようにします。
- ルールを設定しない場合、Google によってランダムに生成されます。
- "プロセスID" を SheetId とする場合は "#{processInstanceId}" のように設定します。
- R2290: EL式による動的な値の挿入
- https://questetra.zendesk.com/hc/ja/articles/360024293072-R2290
- SheetId が既に存在していた場合、自動工程はエラー終了します。
- To Get "ClientId" and "Secret"
- Access to "https://console.developers.google.com/"
- 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
- AutomatedTask UnexpectedResponseError: 400
- { "error": {
- "code": 400,
- "message": "Invalid requests[0].addSheet: シート名「Test」はすでに存在し...",
- "status": "INVALID_ARGUMENT"
- } }
- AutomatedTask UnexpectedResponseError: 400
- { "error": {
- "code": 400,
- "message": "Invalid requests[0].addSheet: Sheet with id 1234 already exists.",
- "status": "INVALID_ARGUMENT"
- } }
*/
//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const strOauthSetting = configs.get ( "OAuth2ConfA" ); // required
const strSpreadsheetId = configs.get ( "StrConfB" ) + ""; // required
const strSheetName = configs.get ( "StrConfC" ) + ""; // required
const strSpecifiedSheetId = configs.get ( "StrConfC2") + "";
const strPocketSheetId = configs.getObject( "SelectConfD" );
//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)
//// == Calculating / 演算 ==
// prepare request1
// ref) https://developers.google.com
// /sheets/api/reference/rest/v4/spreadsheets/request#addsheetrequest
let request1Obj = {};
request1Obj.requests = [];
request1Obj.requests[0] = {};
request1Obj.requests[0].addSheet = {};
request1Obj.requests[0].addSheet.properties = {};
if( strSpecifiedSheetId !== "" ){
request1Obj.requests[0].addSheet.properties.sheetId = strSpecifiedSheetId;
}
request1Obj.requests[0].addSheet.properties.title = strSheetName;
let uri1 = "https://sheets.googleapis.com/v4/spreadsheets/" +
strSpreadsheetId + ":batchUpdate";
const token = httpClient.getOAuth2Token( strOauthSetting );
engine.log( " AutomatedTask ApiRequest Token: prepared" );
let request1 = httpClient.begin(); // HttpRequestWrapper
request1 = request1.bearer( token );
request1 = request1.body( JSON.stringify( request1Obj ), "application/json" );
// post request1
const response1 = request1.post( uri1 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest 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": "1uwGxxxx0UWaxxxxsiCZxxxx6Y3UxxxxO0BgxxxxYRdo",
"replies": [
{
"addSheet": {
"properties": {
"sheetId": 12345,
"title": "Test12345",
"index": 4,
"sheetType": "GRID",
"gridProperties": {
"rowCount": 1000,
"columnCount": 26
}
}
}
}
]
}
*/
const response1Obj = JSON.parse( response1Body );
let strSheetId = response1Obj.replies[0].addSheet.properties.sheetId + "";
engine.log( " AutomatedTask ApiResponse NewSheetId: " + strSheetId );
//// == Data Updating / ワークフローデータへの代入 ==
if( strPocketSheetId !== null ){
engine.setData( strPocketSheetId, strSheetId );
}
} //////// END "main()" /////////////////////////////////////////////////////////////////