「開発」カテゴリーアーカイブ

OneDrive上のエクセル（Excel）ファイルをGoogle Driveに同期してLooker Studioのデータソースにする

イントラネット 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();
}