Microsoft 365 Excel: Insert New Row

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