Microsoft 365 Excel: Update 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/";

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 rowNo = retrieveRowNo();

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(PATCH)
patchData( bookInfo.driveId, bookInfo.fileId, sheetName, rowNo, 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;
}

/**
* config から行番号の値を読み出す、空なら1
* 数字でない文字が入っているか、シートの範囲外である数字の場合はエラー
* シートの範囲: 1 ~ 1048576
* @return {Number} 行番号
*/
function retrieveRowNo() {
const rowNoDef = configs.getObject( "conf_RowNo" );
let rowNo;
if ( rowNoDef === null ) {
rowNo = configs.get( "conf_RowNo" )
} else {
rowNo = engine.findData( rowNoDef );
}
if ( rowNo === "" || rowNo === null){
rowNo = "1";
}
const reg = new RegExp( '^[1-9][0-9]*$' );
if ( !reg.test( rowNo ) ){
throw "Row no must be a positive integer.";
}
rowNo = parseInt(rowNo, 10); //parse to int
if ( rowNo > 1048576 ){
throw "Row no must be 1048576 or below.";
}
return rowNo;
}

/**
* 指定された範囲を考慮しつつ更新データを準備
* 範囲を優先し、範囲外の指定データ項目は無視する
* @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 {Number} rowNo 更新箇所の行番号
* @param {Array} values 更新するデータの配列
* @param {String} range 更新する列範囲
* @param {String} oauth2 OAuth2 設定
*/
function patchData( driveId, bookId, sheetName, rowNo, values, range, oauth2 ){

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

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