Microsoft 365 Excel: 行挿入

Microsoft 365 Excel: Insert New Row

この工程は、シート内の指定された場所に新しい行を挿入し、データを入力します。

Configs for All Step

: 工程名
: メモ

Configs for this Auto Step

conf_OAuth2: C1: OAuth2 設定 *
conf_Url: C2: 入力先のブックの URL *
conf_Title: C3: 入力先のシートのタイトル *^#{EL}
conf_RowNumber: C4: 挿入先の行番号(空の場合は一番上の行)
conf_ValueA: C-A: 挿入行の A 列に追加される値^#{EL}
conf_ValueB: C-B: 挿入行の B 列に追加される値^#{EL}
conf_ValueC: C-C: 挿入行の C 列に追加される値^#{EL}
conf_ValueD: C-D: 挿入行の D 列に追加される値^#{EL}
conf_ValueE: C-E: 挿入行の E 列に追加される値^#{EL}
conf_ValueF: C-F: 挿入行の F 列に追加される値^#{EL}
conf_ValueG: C-G: 挿入行の G 列に追加される値^#{EL}
conf_ValueH: C-H: 挿入行の H 列に追加される値^#{EL}
conf_ValueI: C-I: 挿入行の I 列に追加される値^#{EL}
conf_ValueJ: C-J: 挿入行の J 列に追加される値^#{EL}

Notes

Microsoft 365 Business の Excel で使用できるアドオンです
- 個人用の Excel では使用できません
ブックの URL はブラウザのアドレスバーからコピーするか、上部メニューの「共有」→「リンクのコピー」から取得します
「C4: 挿入先の行番号(空の場合は一番上の行)」にデータ項目を設定する場合、「小数点以下の桁数」は0としてください
1つのセルに入力できる文字数の上限は32,767文字です
1つのシートで使用できる行数の上限は1,048,576行です
- 最終行を既に使用しているシートに対して挿入を行おうとした場合、エラー “NonBlankCellOffSheet” が出力されます
同じブック内に、シート名に含まれる記号の全角／半角が異なるだけの別のシートがある場合、シートの更新が正しく反映されないことがあります
- たとえば、「Sheet 1」（半角スペースを使用）と「Sheet　1」（全角スペースを使用）
- これは、Excel 内部でこれらのシートのデータが自動的に同期されてしまうためです
ブック内のシート数があまりにも多いと、「C3: 入力先のシートのタイトル」で指定したシートを見つけられず、エラー終了する可能性があります

Capture

次のスクリプトが記述されている XML ファイルをダウンロードできます
- excel-row-insert.xml (C) Questetra, Inc. (MIT License)
- Professional のワークフロー基盤では、ファイル内容を改変しオリジナルのアドオン自動工程として活用できます



// 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 / 演算 ==
    const patchRequestBody = makeRequestToAdd(values);

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

    // Access to the API 2nd(Insert Row)
    insertRow(bookInfo, worksheetId, rowNumber, oauth2);

    // Access to the API 3rd(PATCH)
    patchData(bookInfo, worksheetId, rowNumber, patchRequestBody, 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 error = "Row number must be a positive integer no greater than 1048576.";
    const rowNumberDef = configs.getObject("conf_RowNumber");
    let rowNumber;
    if (rowNumberDef === null) { // 固定値で指定
        const rowNumberStr = configs.get("conf_RowNumber");
        if (rowNumberStr === "" || rowNumberStr === null) {
            return 1;
        }
        const reg = new RegExp('^[1-9][0-9]{0,6}$');
        if (!reg.test(rowNumberStr)) {
            throw error;
        }
        rowNumber = parseInt(rowNumberStr, 10); //parse to int
    } else { // 数値型データ項目で指定
        const bigDecimal = engine.findData(rowNumberDef);
        if (bigDecimal === null) {
            return 1;
        }
        try {
            rowNumber = bigDecimal.longValueExact();
        } catch (e) { // 小数点以下が 0 でない
            throw error;
        }
    }
    if (rowNumber < 1 || rowNumber > 1048576) {
        throw error;
    }
    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, "1st 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}`;
}

/**
  * ワークシートの ID を取得する
  * @param {Object} bookInfo
  * @param {String} bookInfo.driveId  ワークブックのドライブ ID
  * @param {String} bookInfo.fileId  ワークブックのファイル ID
  * @param {String} sheetName  シートの名前
  * @param {String} oauth2  OAuth2 設定
  */
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;
}

/**
  * 行を指定シートの指定箇所に挿入する
  * @param {Object} bookInfo
  * @param {String} bookInfo.driveId  挿入先ワークブックのドライブ ID
  * @param {String} bookInfo.fileId  挿入先ワークブックのファイル ID
  * @param {String} worksheetId  挿入先シートの ID
  * @param {Number} rowNumber  挿入箇所の行番号
  * @param {String} oauth2  OAuth2 設定
  */
function insertRow({driveId, fileId}, worksheetId, rowNumber, oauth2) {
    const insertUrl = `${GRAPH_URI}${driveId}/items/${fileId}/workbook/worksheets/${encodeURIComponent(worksheetId)}/range(address='${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 {Object} bookInfo
  * @param {String} bookInfo.driveId  挿入先ワークブックのドライブ ID
  * @param {String} bookInfo.fileId  挿入先ワークブックのファイル ID
  * @param {String} worksheetId  挿入先シートの ID
  * @param {Number} rowNumber  挿入箇所の行番号
  * @param {Object} requestBody  入力するデータの入ったリクエストオブジェクト
  * @param {String} oauth2  OAuth2 設定
  */
function patchData({driveId, fileId}, worksheetId, rowNumber, requestBody, oauth2) {
    const patchUri = `${GRAPH_URI}${driveId}/items/${fileId}/workbook/worksheets/${encodeURIComponent(worksheetId)}/range(address='A${rowNumber}:J${rowNumber}')/`;

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

Microsoft 365 Excel: 行挿入

Microsoft 365 Excel: 行挿入

Notes

Capture

See also

いいね:

Related

Questetra BPM Suite

Microsoft 365 Excel: 行挿入

Microsoft 365 Excel: 行挿入

Notes

Capture

See also

共有:

いいね:

Related

Questetra BPM Suite