Microsoft 365 Excel: Insert New Row
Insert a new row with data at the specified position in a sheet.
2020-09-09 (C) Questetra, Inc. (MIT License)
Configs
  • C1: OAuth2 Setting Name *
  • C2: Target Book URL *
  • C3: Target Sheet Title * #{EL}
  • C4: Row Number to insert new row(When empty, the top row)
  • C-A: Column-A Value of Inserted Row #{EL}
  • C-B: Column-B Value of Inserted Row #{EL}
  • C-C: Column-C Value of Inserted Row #{EL}
  • C-D: Column-D Value of Inserted Row #{EL}
  • C-E: Column-E Value of Inserted Row #{EL}
  • C-F: Column-F Value of Inserted Row #{EL}
  • C-G: Column-G Value of Inserted Row #{EL}
  • C-H: Column-H Value of Inserted Row #{EL}
  • C-I: Column-I Value of Inserted Row #{EL}
  • C-J: Column-J Value of Inserted Row #{EL}
Script
// 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 rowNumber = retrieveRowNumber();
  let values = [];
  values[0] = configs.get( "conf_ValueA" );
  values[1] = configs.get( "conf_ValueB" );
  values[2] = configs.get( "conf_ValueC" );
  values[3] = configs.get( "conf_ValueD" );
  values[4] = configs.get( "conf_ValueE" );
  values[5] = configs.get( "conf_ValueF" );
  values[6] = configs.get( "conf_ValueG" );
  values[7] = configs.get( "conf_ValueH" );
  values[8] = configs.get( "conf_ValueI" );
  values[9] = configs.get( "conf_ValueJ" );

  //// == ワークフローデータの参照 / Data Retrieving ==
  const token  = httpClient.getOAuth2Token( oauth2 );

  //// == Calculating / 演算 ==
  // Access to the API 1st(Get Book Info)
  const bookInfo = getFileInfoByUrl( bookUrl, token );
  
  // Access to the API 2nd(Insert Row)
  insertRow( bookInfo.driveId, bookInfo.fileId, sheetName, rowNumber, token);

  // Access to the API 3rd(PATCH)
  patchData( bookInfo.driveId, bookInfo.fileId, sheetName, rowNumber, values, token);
}

/**
  * config からブックの URL を読み出す、空ならエラー
  * @return {String} ブックの 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;
}

/**
  * config から行番号の値を読み出す、空なら1
  * 数字でない文字が入っているか、シートの範囲外である数字の場合はエラー
  * シートの範囲: 1 ~ 1048576
  * @return {Number} 行番号
  */
function retrieveRowNumber() {
  const rowNumberDef = configs.getObject( "conf_RowNumber" );
  let rowNumber;
  if ( rowNumberDef === null ) {
    rowNumber = configs.get( "conf_RowNumber" )
  }else{
    rowNumber = engine.findData( rowNumberDef );
  }
  if ( rowNumber === "" || rowNumber === null){
    rowNumber = "1";
  }
  const reg = new RegExp( '^[1-9][0-9]*$' );
  if ( !reg.test( rowNumber ) ){
    throw "Row number must be a positive integer.";
  }
  rowNumber = parseInt(rowNumber, 10); //parse to int
  if ( rowNumber > 1048576 ){
    throw "Row number must be 1048576 or below.";
  }
  return rowNumber;
}

/**
  * フォルダの URL からファイル情報(ドライブ ID とファイル ID)を取得し、
  * オブジェクトで返す(URL が空の場合はエラーとする)
  * @param {String} fileUrl  フォルダの URL
  * @param {String} token  OAuth2 トークン
  * @return {Object} fileInfo  ファイル情報 {driveId, fileId}
  */
function getFileInfoByUrl( fileUrl, token ) {
  let fileInfo;
  if ( fileUrl !== "" && fileUrl !== null ) {
    // 分割代入
    const {
      id,
      parentReference: {
        driveId
      }
    } = getObjBySharingUrl( fileUrl, token );
    fileInfo = {driveId: `drives/${driveId}`, fileId: id};
  }
  return fileInfo;
}

/**
  * OneDrive のドライブアイテム(ファイル、フォルダ)のメタデータを取得し、JSON オブジェクトを返す
  * API の仕様:https://docs.microsoft.com/ja-jp/onedrive/developer/rest-api/api/shares_get?view=odsp-graph-online
  * @param {String} sharingUrl  ファイルの共有 URL
  * @param {String} token  OAuth2 トークン
  * @return {Object} responseObj  ドライブアイテムのメタデータの JSON オブジェクト
  */
function getObjBySharingUrl( sharingUrl, token ) {
  if (sharingUrl === "" || sharingUrl === null) {
    throw `Sharing URL is empty.`;
  }

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

  // preparing for API Request
  let apiRequest = httpClient.begin(); // HttpRequestWrapper
  // com.questetra.bpms.core.event.scripttask.HttpClientWrapper
  // Request HEADER (OAuth2 Token, HTTP Basic Auth, etc)
  apiRequest = apiRequest.bearer( token );
  // Access to the API (POST, GET, PUT, etc)
  let response = apiRequest.get( `${GRAPH_URI}shares/${encodedSharingUrl}/driveItem` ); // HttpResponseWrapper
  const httpStatus = response.getStatusCode();
  const accessLog = `---GET request--- ${httpStatus}\n${response.getResponseAsString()}\n`;
  engine.log(accessLog);
  if (httpStatus >= 300) {
    const error = `Failed to get drive item. status: ${httpStatus}`;
    throw error;
  }
  const responseObj = JSON.parse( response.getResponseAsString() );
  return responseObj;
}

/**
  * 共有URLをunpadded base64url 形式にエンコードする
  * @param {String} sharingUrl  共有 URL
  * @return {String} encodedSharingUrl  エンコードされた共有 URL
  */
function encodeSharingUrl( sharingUrl ) {
  let encodedSharingUrl = base64.encodeToUrlSafeString( sharingUrl );
  while ( encodedSharingUrl.slice(-1) === '=' ) {
    encodedSharingUrl = encodedSharingUrl.slice(0,-1);
  }
  encodedSharingUrl = "u!" + encodedSharingUrl;
  return encodedSharingUrl;
}

/**
  * 行を指定シートの指定箇所に挿入する
  * @param {String,String} driveId, bookId  挿入先ファイルのドライブ ID、ファイル ID
  * @param {String} sheetName  挿入先シートの名前
  * @param {Number} rowNumber  挿入箇所の行番号
  * @param {String} token  OAuth2 トークン
  */
function insertRow( driveId, bookId, sheetName, rowNumber, token){
  const insertUrl = `${GRAPH_URI}${driveId}/items/${bookId}/workbook/worksheets/${sheetName}/range(address='${sheetName}!${rowNumber}:${rowNumber}')/insert`;
  const postRequest = {
    "Shift" : "Down"
  };

  let insertNewRow = httpClient.begin()
    .bearer(token)
    .body(JSON.stringify( postRequest ), "application/json")
    .post (insertUrl);
    
  logAndJudgeError(insertNewRow, "POST");
}

/**
  * 指定シートの指定行にデータを入力する
  * @param {String,String} driveId, bookId  挿入先ファイルのドライブ ID、ファイル ID
  * @param {String} sheetName  挿入先シートの名前
  * @param {Number} rowNumber  挿入箇所の行番号
  * @param {Array} values  入力するデータの配列
  * @param {String} token  OAuth2 トークン
  */
function patchData( driveId, bookId, sheetName, rowNumber, values, token){
  const patchUri = `${GRAPH_URI}${driveId}/items/${bookId}/workbook/worksheets/${sheetName}/range(address='${sheetName}!A${rowNumber}:J${rowNumber}')/`;
  const patchRequest = makeRequestToAdd(values);

  let patchToSheet = httpClient.begin()
    .bearer(token)
    .body(JSON.stringify( patchRequest ), "application/json")
    .patch(patchUri); 
  
  logAndJudgeError(patchToSheet, "PATCH");
}

/**
  * 新しい行に追加するデータを、JSON 形式に変換する
  * @param {Array} values  データの入った配列
  * @return {JSON Object} 変換した JSON オブジェクト
  */
function makeRequestToAdd(values){
  let request = {
    values : [[]]
  };
  for(let i = 0; i < 10; i++){
    if(values[i] === "" || values[i] === null){
      request.values[0].push(null);
    }else{
      if(values[i].length > 32767){
        throw "Can't set text over 32,767 character.";
      }
      request.values[0].push(values[i]);
    }
  }
  return request;
}

/**
  * ログの出力と、エラー発生時のスローを行う
  * @param {Object} request  リクエストの応答
  * @param {String} requestType リクエストをどの形式で行ったか("GET" or "POST" or "PATCH")
  */
function logAndJudgeError(request, requestType){
  const response = request.getResponseAsString();
  const status = request.getStatusCode();
  const accessLog = `---${requestType} request--- ${status}
  ${response}
  `;
  engine.log(accessLog);
  if(status >= 300){
    throw `status: ${status}`;
  }
}

Download

Capture

Notes

  1. This add-on is for Excel with Microsoft 365 Business. It does not work for personal Excel accounts.
  2. To get the URL of the book, copy it from the address bar of the browser or select “Share” and click “Copy Link”.
  3. When you set the Data Item in “C4: Row Number to insert new row(When empty, the top row)”, you have to set “Number of decimal places” as 0.
  4. The maximum number of characters you can input into one cell is 32,767.
  5. The maximum number of rows you can use in one sheet is 1,048,576. If you try to insert a the last row into a sheet which is already in use this add-on will return error “NonBlankCellOffSheet”.

%d bloggers like this: