手法
SharePoint上に配置されたデータからインポートする方法は3通り
- Power Automateでcsvをエクセルに追加する方法
- Office Scriptでcsvをエクセルに追加する方法
- Office Scriptでエクセルからエクセルに追加する方法
手法1
手順
- SharePoint上でコンテンツを読み取る
- コンテンツを改行コードをもとに行単位で取得する
- 行単位に分割されたデータをさらに区切り文字(,など)で列単位に分割する
- 1行ずつエクセルへインポートする
メリット
Power Automateで完結する
デメリット
きれいなcsvでないと読み込むことが出来ない。
データ中に文字としての改行コードや区切り文字等が含まれていると処理はできない。
正規表現を使ってなんとか…とおもいつつもPower Automateでは正規表現やワイルドカードを使った文字列処理ができないため、詰み。
手法3
手順
Microsoft:ワークシートを 1 つのブックに結合する
メリット
エクセルからデータをぶっこぬいて、別のエクセルにぶち込む。非常に分かりやすく実装できる。
手法2のscriptよりずっと簡潔で、基本csvがどういう構造であるかというのを気にする必要がない。
デメリット
元データがcsvではないため、どうしてもcsvでなければならない理由があれば、不可。
Scriptを2つ用意しなければならないのが、地味に気になる。
手法2
メリット
必要な処理は全部Office Scriptに寄せようぜ！というスタイルなので、応用はいくらでも利く。
デメリット
scriptそのものが複雑になってしまう。メリットとの天秤。
ソースコード
/**
* csvデータを新規シートに全量importする
* 作成日: xx
* 作成者: xx
* 更新日: xx
* 更新者: xx
*/
// テンプレートシート名
const TEMPLATE_SHEET_NAME = "テンプレートシート";
// CSVファイルの引用符
const QUOTE_CHAR: string = '\"';
// CSVファイルの区切り文字
const DELIMITER_CHAR: string = ',';
/**
* csvデータを新規シートに全量importする
* 【前提条件】
* ・引用符が「"(ダブルクォーテーション)」であること
* ・文字としての引用符は「""(2文字連続している)」で表現されていること
* ・区切り文字が「,(カンマ)」であること
* ・改行コードが「\n または \r\nであること」
* ・csvファイルが破損していないこと (引用符や区切り文字が複数存在するなど)
*
* ・テンプレートシートとcsvファイルの列情報が一致すること
*/
function main(workbook: ExcelScript.Workbook, csvData: string, outputSheetName: string) {
const csvDataArray = convertCSVDataTo2DArray(csvData);
copyTemplateSheet(workbook, TEMPLATE_SHEET_NAME, outputSheetName);
outputCSVDataToSheet(workbook, csvDataArray, outputSheetName);
}
/**
* テンプレートシートをコピーする
* @param {ExcelScript.Workbook} workbook - 自身のworkbookオブジェクト
* @param {string} templateSheetName - テンプレートコピー元のシート名
* @param {string} copiedSheetName - テンプレートコピー先のシート名
*/
function copyTemplateSheet(workbook: ExcelScript.Workbook, templateSheetName: string, copiedSheetName: string) {
const templateWorksheet = workbook.getWorksheet(templateSheetName);
const copiedWorksheet = templateWorksheet.copy(ExcelScript.WorksheetPositionType.end);
copiedWorksheet.setName(copiedSheetName);
}
/**
* csvデータをシートに出力する
* @param {ExcelScript.Workbook} workbook - 自身のworkbookオブジェクト
* @param {string[][]} csvDataArray - 二次元配列のcsvデータ
* @param {string} outputSheetName - テンプレートコピー先のシート名
*/
function outputCSVDataToSheet(workbook: ExcelScript.Workbook, csvDataArray: string[][], outputSheetName: string) {
const worksheet = workbook.getWorksheet(outputSheetName);
// ヘッダー行を削除
csvDataArray.shift();
const rowCount = csvDataArray.length;
const columnCount = csvDataArray[0].length;
const range = worksheet.getRangeByIndexes(1, 0, rowCount, columnCount);
range.setValues(csvDataArray);
}
/**
* csvデータを二次元配列に変換する
* @param {string} csv - 変換前のcsvデータ
* @return {string[][]}
*/
function convertCSVDataTo2DArray(csv: string): string[][]{
// 引用符に含まれるか
let inQuotes: boolean = false;
// 全レコード分の情報
const rows: string[][] = [];
// 1レコード分の情報
let cells: string[] = [];
// 1セル分の情報
let cell: string = "";
// 文字配列の作成
// 処理を簡素化するため、改行コードは一律\nで統一する
const chars: string[] = Array.from(csv.replace(/\r/g, ''));
// 1文字ずつ処理
for (let i = 0; i < chars.length; i++) { const char = chars[i]; // 引用符に含まれない場合 if (!inQuotes) { switch (char) { // 引用符(開始引用符) case QUOTE_CHAR: inQuotes = true; break; // 区切り文字 case DELIMITER_CHAR: // 改行コードを置換する(\n -> \r\n)
// セル内の改行は(\r\n)で扱っているため
cells.push(cell.replace(/\n/g, '\r\n'));
cell = "";
break;
// 改行
case "\n":
// レコードの1行分の情報を配列に追加する
cells.push(cell);
rows.push([...cells]);
cells = [];
cell = "";
break;
default:
throw new Error(`
[convertCSVDataTo2DArray()]
予期せぬ記号を検知しました。csvファイルが破損していないか確認をしてください。
"検出箇所
行番号：${rows.length + 1}
列番号：${cells.length + 1}
セル情報：${cell}
文字：${char}`
);
}
// 引用符に含まれる場合
} else {
switch (char) {
// 引用符
case QUOTE_CHAR:
if (i + 1 < chars.length) {
const next = chars[i + 1];
// 通常文字として処理をする
if (next === QUOTE_CHAR) {
cell += QUOTE_CHAR;
i = i + 1;
// 引用符(終了引用符）として処理をする
} else {
inQuotes = false;
}
}
break;
default:
// 通常文字として扱う
cell += char;
break;
}
}
}
return rows;
}