
Microsoft 365 Excel: Insert New Row
This item inserts a new row with data at the specified position in a sheet.
Configs for All Step
- Step Name
- Note
Configs for this Auto Step
- conf_OAuth2
- C1: OAuth2 Setting *
- conf_Url
- C2: Target Book URL *
- conf_Title
- C3: Target Sheet Title *#{EL}
- conf_RowNumber
- C4: Row Number to insert new row(When empty, the top row)
- conf_ValueA
- C-A: Column-A Value of Inserted Row#{EL}
- conf_ValueB
- C-B: Column-B Value of Inserted Row#{EL}
- conf_ValueC
- C-C: Column-C Value of Inserted Row#{EL}
- conf_ValueD
- C-D: Column-D Value of Inserted Row#{EL}
- conf_ValueE
- C-E: Column-E Value of Inserted Row#{EL}
- conf_ValueF
- C-F: Column-F Value of Inserted Row#{EL}
- conf_ValueG
- C-G: Column-G Value of Inserted Row#{EL}
- conf_ValueH
- C-H: Column-H Value of Inserted Row#{EL}
- conf_ValueI
- C-I: Column-I Value of Inserted Row#{EL}
- conf_ValueJ
- C-J: Column-J Value of Inserted Row#{EL}
Notes
- This add-on is for Excel with Microsoft 365 Business
- It does not work for personal Excel accounts
- To get the URL of the book, copy it from the address bar of the browser or select “Share” and click “Copy Link”
- 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
- The maximum number of characters you can input into one cell is 32,767
- The maximum number of rows you can use in one sheet is 1,048,576
- If you try to insert into a sheet which already uses the last row this add-on will return error “NonBlankCellOffSheet”
- When the book has multiple sheets whose names differ only by whether a symbol is single-byte or multi-byte, the sheet update may not be reflected correctly
- For example, “Sheet 1” (with a single-byte space) and “Sheet 1” (with a multi-byte space)
- This is because Excel automatically syncs the data in these sheets
- In case the book has an excessively large number of sheets, this item may fail to find the sheet specified in “C3: Target Sheet Title” and result in error
Capture

See also
Script (click to open)
- An XML file that contains the code below is available to download
- excel-row-insert.xml (C) Questetra, Inc. (MIT License)
- If you are using Professional, you can modify the contents of this file and use it as your own add-on auto step
// 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;
}