イントラネット Windows ドメイン内にあるエクセル(Excel)ファイルを Looker Studio のデータソースにするのに思いの外手間がかかったので、方法を書いておく。
方法概要
Google Drive Desktop をPCにインストールし、OneDrive 内のエクセルファイルを Google Drive に同期。
Google App Script で Google Drive 上に同期されているエクセルファイルを Google Spreadsheet に変換。テーブル項目となる1行目を除いて Looker Studio のデータソースとした Spreadsheet ファイルにコピーする。この処理をApp Script のトリガーで自動化する。
App Script のコード
/* エクセル -> Google Spreadsheet -> Looker Studio に定期的・自動同期 */
function main() {
// ファイルID(Google Drive でファイルを開いてURLから取る)
var SourceExcelID = "___LONG_RANDOM_STRING_1_____";
// OneDrive から同期した .xlsx ファイル
var SourceExcelSheetName = "Sheet 1"; // .xlsx ファイル内で使うシート名
var DataStudioSouceID = "___LONG_RANDOM_STRING_2_____";
// Looker Studio でデータソースにする Spreadsheet。予め作っておく。
var TemporarySheetID = "___LONG_RANDOM_STRING_3_____";
// 変換用中間 Spreadsheet ファイル。予め作っておく。
// ファイルを扱う API を2種類使う。"Drive API" と "Sheets API" をApp Script の「サービス」から追加しておく
// Drive API でファイル変換
// Sheets API で内容操作
// エクセル(Excel)ファイル
var theSourceDriveApp = DriveApp.getFileById(SourceExcelID);
var theSourceDrive = Drive.Files.get(SourceExcelID);
// 変換用中間ファイル
var theTempDriveApp = DriveApp.getFileById(TemporarySheetID);
var theTempDrive = Drive.Files.get(TemporarySheetID);
// Looker Studio データソースファイル
var theDataStudioSouceDriveApp = DriveApp.getFileById(DataStudioSouceID);
// ファイル変換 Excel -> Google Spreadsheet
// ファイルを丸ごと変換する
Drive.Files.update({
title: theTempDrive.originalFilename,
mimeType: MimeType.GOOGLE_SHEETS
}, theTempDrive.id, theSourceDriveApp.getBlob());
// 変換した中間ファイルから、Looker Studio ソースファイルに必要なデータをコピーする
// 変換した中間ファイルの対象シートからデータ部分のみを取り出す。
var theTempSpreadsheetDriveApp = SpreadsheetApp.open(theTempDriveApp);
var theTempSheet = theTempSpreadsheetDriveApp.getSheetByName(SourceExcelSheetName);
var theValuesList = theTempSheet.getRange("A3:I70").getValues();
// Looker Studio データソースファイルの対象シート
var theTargetSpreadsheetDriveApp = SpreadsheetApp.open(theDataStudioSouceDriveApp);
var theTargetSheet = theTargetSpreadsheetDriveApp.getSheets()[0];
// タイトル行を変えないように、取り出したデータを貼り付け
theTargetSheet.getRange("A2:I69").setValues(theValuesList);
SpreadsheetApp.flush();
}