Microsoft 365 Excel: Insert New Row
Insert a new row with data at the specified position in a sheet.
Configs
  • C1: OAuth2 Setting *
  • 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 (click to open)

// 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" );

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

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

/**
  * 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} oauth2  OAuth2 設定
  * @return {Object} fileInfo  ファイル情報 {driveId, fileId}
  */
function getFileInfoByUrl( fileUrl, oauth2 ) {
  let fileInfo;
  if ( fileUrl !== "" && fileUrl !== null ) {
    // 分割代入
    const {
      id,
      parentReference: {
        driveId
      }
    } = getObjBySharingUrl( fileUrl, oauth2 );
    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} oauth2  OAuth2 設定
  * @return {Object} responseObj  ドライブアイテムのメタデータの JSON オブジェクト
  */
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 );
}

/**
  * 共有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);
  }
  return `u!${encodedSharingUrl}`;
}

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

  const response = httpClient.begin()
    .authSetting( oauth2 )
    .body( JSON.stringify(requestBody), "application/json" )
    .post( insertUrl );
    
  logAndJudgeError(response, "POST");
}

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

  const response = httpClient.begin()
    .authSetting( oauth2 )
    .body( JSON.stringify(requestBody), "application/json" )
    .patch( patchUri );
  
  logAndJudgeError(response, "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 {HttpResponseWrapper} response  リクエストの応答
  * @param {String} requestType リクエストをどの形式で行ったか("GET" or "POST" or "PATCH")
  * @return {String} responseStr レスポンスの文字列
  */
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;
}

Download

2021-05-18 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/excel-row-insert/
The Addon-import feature is available with Professional or Enterprise edition.

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”.

Capture

See also

%d bloggers like this: