Microsoft 365 Excel: 行追加 (Microsoft 365 Excel: Append New Row)
Excel シート末尾に1行追加し、その各セルにデータを入力します。入力範囲は最大10列まで指定できます。
Configs
  • C1: OAuth2 設定 *
  • C2: 入力先のブックの URL *
  • C3: 入力先のシートのタイトル *#{EL}
  • C4: 追加する列範囲 (例 “A:O”) (最大 15 列) *
  • C5_1: 末尾行の 1 列目に追加される値#{EL}
  • C5_2: 末尾行の 2 列目に追加される値#{EL}
  • C5_3: 末尾行の 3 列目に追加される値#{EL}
  • C5_4: 末尾行の 4 列目に追加される値#{EL}
  • C5_5: 末尾行の 5 列目に追加される値#{EL}
  • C5_6: 末尾行の 6 列目に追加される値#{EL}
  • C5_7: 末尾行の 7 列目に追加される値#{EL}
  • C5_8: 末尾行の 8 列目に追加される値#{EL}
  • C5_9: 末尾行の 9 列目に追加される値#{EL}
  • C5_10: 末尾行の 10 列目に追加される値#{EL}
  • C5_11: 末尾行の 11 列目に追加される値#{EL}
  • C5_12: 末尾行の 12 列目に追加される値#{EL}
  • C5_13: 末尾行の 13 列目に追加される値#{EL}
  • C5_14: 末尾行の 14 列目に追加される値#{EL}
  • C5_15: 末尾行の 15 列目に追加される値#{EL}
  • C6: 行追加した行番号の値を保存するデータ項目 (更新)
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/";

const COLUMN_NUM = 15;

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 range = configs.get( "conf_Range" );

  let values = [];
  retrieveValues( range, values );

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

  // Access to the API 2nd(Get Last Row Number)
  const lastRow = getLastRowNumber( bookInfo.driveId, bookInfo.fileId, sheetName, oauth2 );
  const rowNumber = lastRow + 1;

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

  const dataId = configs.get( "conf_RowNum" );
  if ( dataId ){
    engine.setDataByNumber( dataId, rowNumber + "" );
  }
}

/**
  * 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;
}

/**
  * 指定された範囲を考慮しつつ追加データを準備
  * 範囲を優先し、範囲外の指定データ項目は無視する
  * @param {Array} range  範囲の配列
  * @param {Array} values  追加データの配列
  */
function retrieveValues( range, values ) {

  //rangeの範囲に合わせてデータを準備、最大数の制限あり
  const rangeArr = range.split(':');
  const from = convertA1toR1C1(rangeArr[0] + "");
  const to = convertA1toR1C1(rangeArr[1] + "");
  const target = to - from + 1;
  let limit = COLUMN_NUM;
  if (target <= COLUMN_NUM) {
    limit = target;
  }

  for (let i = 0; i < limit; i++) {
    const columnConfigName = `conf_Column${i+1}`;
    const columnValue = configs.get( columnConfigName );
    values.push( columnValue );
  }
}

/**
  * フォルダの 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 {String} oauth2  OAuth2 設定
  */
function getLastRowNumber( driveId, bookId, sheetName, oauth2 ){

  const getUri = `${GRAPH_URI}${driveId}/items/${bookId}/workbook/worksheets/${sheetName}/usedRange/`;
  const response = httpClient.begin()
    .authSetting( oauth2 )
    .get( getUri );

  const responseStr = logAndJudgeError(response, "GET");
  const jsonObj = JSON.parse( responseStr );

  //取得できたデータの範囲
  // Index は0始まり、Count は列や行数
  // 例えば rowIndex が2で、rowCount が3なら、3行目始まりで5行目まで値あり
  const rowIndex = jsonObj.rowIndex;
  const rowCount = jsonObj.rowCount;

  return rowIndex + rowCount;
}

/**
  * 指定シートの指定行にデータを入力する
  * @param {String,String} driveId, bookId  挿入先ファイルのドライブ ID、ファイル ID
  * @param {String} sheetName  挿入先シートの名前
  * @param {Number} rowNumber  挿入箇所の行番号
  * @param {Array} values  挿入するデータの配列
  * @param {String} range  挿入する列範囲
  * @param {String} oauth2  OAuth2 設定
  */
function patchData( driveId, bookId, sheetName, rowNumber, values, range, oauth2 ){

  const rangeArr = range.split(':');

  const patchUri = `${GRAPH_URI}${driveId}/items/${bookId}/workbook/worksheets/${sheetName}/range(address='${sheetName}!${rangeArr[0]}${rowNumber}:${rangeArr[1]}${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 < values.length; 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;
}

/**
  * 列を指定するアルファベットを数値変換する(例:A→1、C→3)
  * @param {String} columnString 列を指定するアルファベット
  * @return {Number} num アルファベットに対応した数値
  */
function convertA1toR1C1(columnString) {
  const RADIX = 26;
  const A = 'A'.charCodeAt(0);
  let str = columnString.toUpperCase();
  let num = 0;
  let strLength = str.length;
  for (let i = 0; i < strLength; i++) {
    num = (num * RADIX) + (str.charCodeAt(i) - A + 1);
  }
  return num;
}

/**
  * ログの出力と、エラー発生時のスローを行う
  * @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

2022-10-16 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/ja/bpmn-icons/excel-row-append/
Addonファイルのインポートは Professional でのみご利用いただけます
自由改変可能な JavaScript (ECMAScript) コードです。いかなる保証もありません。

Notes

  • Microsoft365 系のサービスとの連携設定について
  • 「追加する列範囲」と「末尾行の〇行目に追加される値」の指定が不整合となるケースは、「追加する列範囲」が優先されます。
    例えば、列範囲の指定が「A:C」(3行分)の場合、4行目以降の追加される値は無視されます。
  • 値または書式設定が割り当たっているセル範囲から末尾行が判定されます。(内部的に usedRange() を利用)
  • シート名にカッコ等の記号が入っている場合にはエラーになることがあります。エラーとなった場合にはシート名の変更を検討してください。

Capture

Appendix

このアドオンXMLの内部処理としては、値または書式設定が割り当たっているセル範囲から末尾行を判定(内部的に usedRange() を利用)し、行追加を行います。 そのため、複数のプロセスから同時に実行された場合、競合により処理が正常に完了しない可能性があります。 処理の頻度等から競合の可能性が考えられる場合、以下のフロー図例のような工夫を検討してください。
例:行追加ではA列にIDだけを入力させ、 「Microsoft 365 Excel: 行取得」でA列にIDが入力されているかを確認し、 確認OKなら「Microsoft 365 Excel: 行更新」で残りの値を入力する。

See also

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