DriveAPIを使用せずにxlsxファイルをGASで読み込む

概要

DriveAPIを利用せず, GoogleDrive上のxlsxファイルを読み込むためのコードです.

目次

• 概要
  • 目次
  • 使用データ
  • Google Driveに格納されたxlsxのURL
  • コード
  • スプレッドシートに転記する例

使用データ

以下をxlsxファイルに書き込み, Google Driveに格納したファイルを用いる

商品	価格
a,b	900
b	500
c	800
b,c	1500

Google Driveに格納されたxlsxのURL

xlsxファイルをブラウザ上で展開(スプレッドシートで展開)すると以下のようなURLが表示される.

https://docs.google.com/spreadsheets/d/{ここに記載されるIDを利用}/edit?gid=~#gid=~

URLに表示されるIDをコードで利用する.

コード

以下でxlsxファイルをCSV形式で読み込むことができる.

const xlsxId = "xlsxのIDを記載"
const xlsxDownloadUrl = `https://docs.google.com/spreadsheets/d/${xlsxId}/export?format=csv`
const responseText = UrlFetchApp.fetch(xlsxDownloadUrl,{
  headers:{
    // Bearer の後のスペースは空けておく
    'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
  }
}).getContentText();

GAS上で実行するとresponseTextは以下のように表示される.

データ中にカンマがある場合はダブルクォーテーションで囲まれる.

スプレッドシートに転記する例

スプレッドシートに転記する例を示す.

var GET_SHEET_LIST = [
  {downloadId: "IDを記載", outputSheetName: "出力先のシートを指定"},
  // 複数指定可能

  // {downloadId: "", outputSheetName: ""},
]

function fetchXlsxData() {
  for (const row of GET_SHEET_LIST){
    const xlsxDownloadUrl = `https://docs.google.com/spreadsheets/d/${row["downloadId"]}/export?format=csv`
    const responseText = UrlFetchApp.fetch(xlsxDownloadUrl,{
      headers:{
        'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(),
      }
    }).getContentText();

    // シートを指定
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(row["outputSheetName"])
    // 書き込み先のシートのデータを全て削除
    sheet.clear()
    // ダブルクォーテーションで囲まれたカンマでは区切らないように指定
    // =(?:\"[^\"]*\")　でダブルクォーテーションで囲まれた箇所を除外
    var writeArray = responseText.split('\n').map((row, index) => {
      return row.split(/,(?=(?:\"[^\"]*\")*[^\"]*$)/);
    })
    // シートにデータを全て書き込む
    sheet.getRange(1,1,writeArray.length, writeArray[0].length).setValues(writeArray)
  }
}

スプレッドシートには以下のように格納される.