Microsoft 365 Excel: Append New Row
Adds a row to the end of an Excel sheet and enters data in each of its cells. The input range can be up to 10 columns.
Configs
  • C1: OAuth2 Setting *
  • C2: Target Book URL *
  • C3: Target Sheet Title *#{EL}
  • C4: Column Range to Append (e.g. “A:J”)(Up to 10 columns) *
  • C5_1: 1st Column Value of Inserted Row#{EL}
  • C5_2: 2nd Column Value of New Row#{EL}
  • C5_3: 3rd Column Value of New Row#{EL}
  • C5_4: 4th Column Value of New Row#{EL}
  • C5_5: 5th Column Value of New Row#{EL}
  • C5_6: 6th Column Value of New Row#{EL}
  • C5_7: 7th Column Value of New Row#{EL}
  • C5_8: 8th Column Value of New Row#{EL}
  • C5_9: 9th Column Value of New Row#{EL}
  • C5_10: 10th Column Value of New Row#{EL}
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 = 10;

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

/**
* 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-06-20 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/bpmn-icons/excel-row-append/
The Addon-import feature is available with Professional edition.
Freely modifiable JavaScript (ECMAScript) code. No warranty of any kind.

Notes

  • About the settings for integration with Microsoft365 services
  • In cases where there is a mismatch between the column range to be added and the specification of the value to be added on line 0 of the tail line, the column range to be added takes precedence.
    For example, if the column range is specified as “A:C” (for 3 lines), the values to be added after the fourth line are ignored.
  • If there is a cell that does not contain a value but is formatted, the row containing that cell is also considered to be used and the last row is identified. (Internally useRange() is used to identify the last row)

Capture

See also

%d bloggers like this: