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を記載", outputSheetId:"出力先のSSのIDを指定", outputSheetName: "出力先のシートを指定"},
  // 複数指定可能

  // {downloadId: "", outputSheetId:"", 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.openById(row["outputSheetId"]).getSheetByName(row["outputSheetName"])
    // 書き込み先のシートのデータを全て削除
    sheet.clear()
    // CSVテキストを二次元配列に格納
    var writeArray = Utilities.parseCsv(responseText)    
    // ~2025/02/09 追記~ 
    // コメントでUtilities.parseCsv()について教えていただいたので, 上記コードに変更
    // 以下は元々記載していたコード
    // ダブルクォーテーションで囲まれたカンマでは区切らないように指定
    // =(?:\"[^\"]*\")　でダブルクォーテーションで囲まれた箇所を除外
    // var writeArray = responseText.split('\n').map((row, index) => {
    //   return row.split(/,(?=(?:\"[^\"]*\")*[^\"]*$)/);
    // })
    
    // シートにデータを全て書き込む
    sheet.getRange(1,1,writeArray.length, writeArray[0].length).setValues(writeArray)
  }
}

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