Microsoft 365 Excel: Multiple Cell Values, Bulk Update (Range Specification)

// OAuth2 config sample at [OAuth 2.0 Setting]
// - Authorization Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/authorize
// - Token Endpoint URL: https://login.microsoftonline.com/common/oauth2/v2.0/token
// - Scope: https://graph.microsoft.com/Files.ReadWrite.All offline_access
// - Consumer Key: (Get by Microsoft Azure Active Directory)
// - Consumer Secret: (Get by Microsoft Azure Active Directory)

const GRAPH_URI = "https://graph.microsoft.com/v1.0/";

main();
function main(){
  //// == Config Retrieving / 工程コンフィグの参照 ==
  const oauth2 = configs.get( "conf_OAuth2" );
  const bookUrl = retrieveBookUrl();
  const sheetName = configs.get( "conf_Title" );
  if(sheetName === "" || sheetName === null){
    throw "Sheet Title is empty.";
  }

  const cell1 = configs.get( "conf_CellFrom" );
  const cell2 = configs.get( "conf_CellTo" );
  const tsv = configs.get( "conf_Tsv" );
  const json = retrieveJson(tsv);
//engine.log("json:" + json + "");

  //// == Calculating / 演算 ==
  // Access to the API 1st(Get Book Info)
  const bookInfo = getFileInfoByUrl( bookUrl, oauth2 );
  const worksheetId = getWorksheetId(bookInfo, sheetName, oauth2);

  // Access to the API 2nd(PATCH)
  patchData( bookInfo, worksheetId, cell1,cell2, json, oauth2);
}

/**
  * Read the book URL from the config, if empty it will throw an error
  * @return {String} Book URL
  */
function retrieveBookUrl() {
  const bookUrlDef = configs.getObject( "conf_Url" );
  let bookUrl;
  if ( bookUrlDef === null ) {
    bookUrl = configs.get( "conf_Url" )
  }else{
    bookUrl = engine.findData( bookUrlDef );
  }
  if ( bookUrl === "" || bookUrl === null){
    throw "Book URL is empty."
  }
  return bookUrl;
}

/**
  * Generate update data JSON string based on the setting TSV
  * @param {String} text  Original TSV
  * @return {String} JSON String
  */
function retrieveJson(tsv) {
  let json = '{"values" : [';
  const arrLine = tsv.split(/\r\n|\n/);
engine.log("arrLine:"+arrLine.length)
  for ( let i = 0; i < arrLine.length; i++ ) {
    if ( i > 0 ) {
      json += ','
    }
    json += '['
    const arrCell = arrLine[i].split("\t");
engine.log("arrCell:"+arrCell.length)
    for ( let j = 0; j < arrCell.length; j++ ) {
      if ( j > 0 ) {
        json += ','
      }
      if ( arrCell[j] === "" ) {
        json += 'null'
      } else {
        json += '"' + arrCell[j] + '"'
      }
    }
    json += ']'
  }
  json += ']}';
  return json;
}

/**
  * Get the file information (drive ID and file ID) from the folder URL,
  * Return an object (if the URL is empty, an error occurs)
  * @param {String} fileUrl  Folder URL
  * @param {String} oauth2  OAuth2 Settings
  * @return {Object} fileInfo  File information {driveId, fileId}
  */
function getFileInfoByUrl( fileUrl, oauth2 ) {
  let fileInfo;
  if ( fileUrl !== "" && fileUrl !== null ) {
    // Division and assignment
    const {
      id,
      parentReference: {
        driveId
      }
    } = getObjBySharingUrl( fileUrl, oauth2 );
    fileInfo = {driveId: `drives/${driveId}`, fileId: id};
  }
  return fileInfo;
}

/**
  * Retrieve OneDrive drive item (file, folder) metadata and return JSON object
  * API Specifications：https://docs.microsoft.com/ja-jp/onedrive/developer/rest-api/api/shares_get?view=odsp-graph-online
  * @param {String} sharingUrl  Shared URL for the file
  * @param {String} oauth2  OAuth2 Settings
  * @return {Object} responseObj  Drive item metadata JSON object
  */
function getObjBySharingUrl( sharingUrl, oauth2 ) {
  if (sharingUrl === "" || sharingUrl === null) {
    throw `Sharing URL is empty.`;
  }

  // encoding sharing URL
  const encodedSharingUrl = encodeSharingUrl(sharingUrl);

  // API Request
  const response = httpClient.begin()
    .authSetting( oauth2 )
    .get( `${GRAPH_URI}shares/${encodedSharingUrl}/driveItem` );

  const responseStr = logAndJudgeError(response, "GET");

  return JSON.parse( responseStr );
}

/**
  * Encode the sharing URL into unpadded base64url format
  * @param {String} sharingUrl  Sharing URL
  * @return {String} encodedSharingUrl  Encoded Sharing URL
  */
function encodeSharingUrl( sharingUrl ) {
  let encodedSharingUrl = base64.encodeToUrlSafeString( sharingUrl );
  while ( encodedSharingUrl.slice(-1) === '=' ) {
    encodedSharingUrl = encodedSharingUrl.slice(0,-1);
  }
  return `u!${encodedSharingUrl}`;
}

/**
  * Get the ID of a worksheet
  * @param {Object} bookInfo
  * @param {String} bookInfo.driveId  Workbook Drive ID
  * @param {String} bookInfo.fileId  The file ID of the workbook.
  * @param {String} sheetName  Sheet name
  * @param {String} oauth2  OAuth2 Settings
  */
function getWorksheetId({driveId, fileId}, sheetName, oauth2) {
    const getWorksheetsUrl = `${GRAPH_URI}${driveId}/items/${fileId}/workbook/worksheets`;
    const response = httpClient.begin()
        .authSetting(oauth2)
        .get(getWorksheetsUrl);

    const responseStr = logAndJudgeError(response, "2nd GET");
    const jsonRes = JSON.parse(responseStr);
    const worksheet = jsonRes.value.find(worksheet => worksheet.name === sheetName);
    if (worksheet === undefined) {
        throw 'Worksheet not found.';
    }
    return worksheet.id;
}

/**
  * Update the data in the specified cell in the specified sheet
  * @param {Object} bookInfo
  * @param {String} bookInfo.driveId  Workbook Drive ID
  * @param {String} bookInfo.fileId  The file ID of the workbook.
  * @param {String} worksheetId  Sheet ID
  * @param {String} cell1  Update range cell from
  * @param {String} cell2  Update range cell to
  * @param {String} json  Update data JSON string
  * @param {String} oauth2  OAuth2 Settings
  */
function patchData( {driveId, fileId}, worksheetId, cell1, cell2, json, oauth2 ){
  const patchUri = `${GRAPH_URI}${driveId}/items/${fileId}/workbook/worksheets/${encodeURIComponent(worksheetId)}/range(address='${cell1}:${cell2}')/`;

  const response = httpClient.begin()
    .authSetting( oauth2 )
    .body( json, "application/json" )
    .patch( patchUri );
  
  logAndJudgeError(response, "PATCH");
}

/**
  * Convert the data to be added to the new row into JSON format
  * @param {Array} values  An array containing data
  * @return {JSON Object} The converted JSON object
  */
function makeRequestToAdd(values){
  let request = {
    values : [[]]
  };

    if(values[0] === "" || values[0] === null){
      request.values[0].push(null);
    } else {
      if(values[0].length > 32767){
        throw "Can't set text over 32,767 character.";
      }
      request.values[0].push(values[0]);
    }

  return request;
}

/**
  * Outputs logs and throws an error when an error occurs.
  * @param {HttpResponseWrapper} response  Response to a request
  * @param {String} requestType In what format did you make the request?("GET" or "POST" or "PATCH")
  * @return {String} responseStr Response String
  */
function logAndJudgeError(response, requestType){
  const responseStr = response.getResponseAsString();
  const status = response.getStatusCode();
  if(status >= 300){
    const accessLog = `---${requestType} request--- ${status}\n${responseStr}\n`;
    engine.log(accessLog);
    throw `Failed in ${requestType} request. status: ${status}`;
  }
  return responseStr;
}