Microsoft 365 Excel: 行挿入 (Microsoft 365 Excel: Insert New Row)
シート内の指定された場所に新しい行を挿入し、データを入力します。
2020-09-09 (C) Questetra, Inc. (MIT License)
Configs
  • C1: OAuth2 設定名 *
  • C2: 入力先のブックの URL *
  • C3: 入力先のシートのタイトル * #{EL}
  • C4: 挿入先の行番号(空の場合は一番上の行)
  • C-A: 挿入行の A 列に追加される値 #{EL}
  • C-B: 挿入行の B 列に追加される値 #{EL}
  • C-C: 挿入行の C 列に追加される値 #{EL}
  • C-D: 挿入行の D 列に追加される値 #{EL}
  • C-E: 挿入行の E 列に追加される値 #{EL}
  • C-F: 挿入行の F 列に追加される値 #{EL}
  • C-G: 挿入行の G 列に追加される値 #{EL}
  • C-H: 挿入行の H 列に追加される値 #{EL}
  • C-I: 挿入行の I 列に追加される値 #{EL}
  • C-J: 挿入行の J 列に追加される値 #{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. Microsoft 365 Business の Excel で使用できるアドオンです。個人用の Excel では使用できません。
  2. ブックの URL はブラウザのアドレスバーからコピーするか、上部メニューの「共有」→「リンクのコピー」から取得します。
  3. 「C4: 挿入先の行番号(空の場合は一番上の行)」にデータ項目を設定する場合、「小数点以下の桁数」は0としてください。
  4. 1つのセルに入力できる文字数の上限は32,767文字です。
  5. 1つのシートで使用できる行数の上限は1,048,576行です。最終行を既に使用しているシートに対して挿入を行おうとした場合、エラー “NonBlankCellOffSheet” が出力されます。

%d人のブロガーが「いいね」をつけました。