Google Sheets: Spreadsheets Cells Value, Bulk Update (range)

//// == Calculating / 演算 ==

//// Overwrite the value in the cell
/// Sets values in a range of a spreadsheet.
/// (Google Sheets > API v4)
/// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate?hl=ja

// request1, prepare

let request1Obj = {};
    request1Obj.valueInputOption = "USER_ENTERED";
    request1Obj.data = [];
    request1Obj.data[0] = {};
    request1Obj.data[0].range = "'" + strSheetName + "'!" + cell1 + ":" + cell2;
    request1Obj.data[0].majorDimension = "COLUMNS";
    request1Obj.data[0].values = [];

/*
//転置してない場合
    const arrLine = tsv.split(/\r\n|\n/);
 engine.log("arrLine:"+arrLine.length)
    for ( let i = 0; i < arrLine.length; i++ ) {
 engine.log("i:"+i)
      let array = [];
      const arrCell = arrLine[i].split("\t");
 engine.log("arrCell:"+arrCell.length)
      for ( let j = 0; j < arrCell.length; j++ ) {
 engine.log("j:"+j)
 engine.log("arrCell[j]:"+arrCell[j])
        array.push(arrCell[j]);
      }
      request1Obj.data[0].values[i] = array;
    }
*/

//TSVを転置しつつ二次元配列に展開
    const arrLine = tsv.split(/\r\n|\n/);
// engine.log("arrLine:"+arrLine.length)
    for ( let i = 0; i < arrLine.length; i++ ) {
// engine.log("i:"+i)
      const arrCell = arrLine[i].split("\t");
// engine.log("arrCell:"+arrCell.length)
      for ( let j = 0; j < arrCell.length; j++ ) {
// engine.log("j:"+j)
// engine.log("arrCell[j]:"+arrCell[j])
        if (i == 0) {
          let array = [];
          request1Obj.data[0].values[j] = array;
          request1Obj.data[0].values[j][i] = arrCell[j]
        } else {
          request1Obj.data[0].values[j][i] = arrCell[j]
        }
      }
    }

engine.log("JSON.stringify( request1Obj ):" + JSON.stringify( request1Obj ) + "");

let postUri1  = "https://sheets.googleapis.com/v4/spreadsheets/" +
               strFileId + "/values:batchUpdate";
let request1 = httpClient.begin(); // HttpRequestWrapper
    request1 = request1.authSetting( oauth2 ); // with "Authorization: Bearer XX"
    request1 = request1.body( JSON.stringify( request1Obj ), "application/json" );

// request1, try
const response1     = request1.post( postUri1 ); // HttpResponseWrapper
  engine.log( " AutomatedTask ApiRequest1 Start: " + postUri1 );
const response1Code = response1.getStatusCode() + "";
const response1Body = response1.getResponseAsString() + "";
  engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1Code + "\n" + response1Body + "\n" );
}

const response1Obj = JSON.parse( response1Body );
engine.log( " AutomatedTask ApiRequest1 updateRange: " + response1Obj.updatedRange );


//// == Data Updating / ワークフローデータへの代入 ==
// (No Output except Console Log and response0Obj.values[0][0] )


} //////// END "main()" /////////////////////////////////////////////////////////////////


ScriptEncoded
// GraalJS Script (engine type: 3)


//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const oauth2 = configs.getObject( "AuthzConfA" ); // required
const strFileId       = configs.get( "StrConfB1" ) + ""; // required
const strSheetName    = configs.get( "StrConfB2" ) + "";

const cell1           = configs.get( "StrConfC1" );
const cell2           = configs.get( "StrConfC2" );
const tsv             = configs.get( "StrConfC3" );

//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)


//// == Calculating / 演算 ==

//// Overwrite the value in the cell
/// Sets values in a range of a spreadsheet.
/// (Google Sheets > API v4)
/// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate?hl=ja

// request1, prepare

let request1Obj = {};
    request1Obj.valueInputOption = "USER_ENTERED";
    request1Obj.data = [];
    request1Obj.data[0] = {};
    request1Obj.data[0].range = "'" + strSheetName + "'!" + cell1 + ":" + cell2;
    request1Obj.data[0].majorDimension = "COLUMNS";
    request1Obj.data[0].values = [];

/*
//転置してない場合
    const arrLine = tsv.split(/\r\n|\n/);
 engine.log("arrLine:"+arrLine.length)
    for ( let i = 0; i < arrLine.length; i++ ) {
 engine.log("i:"+i)
      let array = [];
      const arrCell = arrLine[i].split("\t");
 engine.log("arrCell:"+arrCell.length)
      for ( let j = 0; j < arrCell.length; j++ ) {
 engine.log("j:"+j)
 engine.log("arrCell[j]:"+arrCell[j])
        array.push(arrCell[j]);
      }
      request1Obj.data[0].values[i] = array;
    }
*/

//TSVを転置しつつ二次元配列に展開
    const arrLine = tsv.split(/\r\n|\n/);
// engine.log("arrLine:"+arrLine.length)
    for ( let i = 0; i < arrLine.length; i++ ) {
// engine.log("i:"+i)
      const arrCell = arrLine[i].split("\t");
// engine.log("arrCell:"+arrCell.length)
      for ( let j = 0; j < arrCell.length; j++ ) {
// engine.log("j:"+j)
// engine.log("arrCell[j]:"+arrCell[j])
        if (i == 0) {
          let array = [];
          request1Obj.data[0].values[j] = array;
          request1Obj.data[0].values[j][i] = arrCell[j]
        } else {
          request1Obj.data[0].values[j][i] = arrCell[j]
        }
      }
    }

engine.log("JSON.stringify( request1Obj ):" + JSON.stringify( request1Obj ) + "");

let postUri1  = "https://sheets.googleapis.com/v4/spreadsheets/" +
               strFileId + "/values:batchUpdate";
let request1 = httpClient.begin(); // HttpRequestWrapper
    request1 = request1.authSetting( oauth2 ); // with "Authorization: Bearer XX"
    request1 = request1.body( JSON.stringify( request1Obj ), "application/json" );

// request1, try
const response1     = request1.post( postUri1 ); // HttpResponseWrapper
  engine.log( " AutomatedTask ApiRequest1 Start: " + postUri1 );
const response1Code = response1.getStatusCode() + "";
const response1Body = response1.getResponseAsString() + "";
  engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1Code + "\n" + response1Body + "\n" );
}

const response1Obj = JSON.parse( response1Body );
engine.log( " AutomatedTask ApiRequest1 updateRange: " + response1Obj.updatedRange );


//// == Data Updating / ワークフローデータへの代入 ==
// (No Output except Console Log and response0Obj.values[0][0] )


} //////// END "main()" /////////////////////////////////////////////////////////////////