Google Apps Scriptでデータをテーブル化する方法【GAS】
2025年5月2日、GASのメソッドではなくREST APIであるSheets APIにて先行して「データをテーブル化」といった一連のテーブル関係の機能が装備されました。
GASからはSheets APIが直接利用できるので、一連の作業をこのAPIを使って操作してみようと思います。CSVを取り込んで新規にシートを作成し、即時にテーブル化といった一連の作業を自動化することが可能です。
目次
今回利用するサンプル等
- GASでテーブル操作 - Google Spreadsheet
- Google Sheets API - Tables
出たばかりのAPIであるためと、GASのメソッドではないのでまだ事例が無いため、ちょっと苦戦しました。以前、Sheets APIで速度アップの項目で利用していますが、同じような流れで実装を試みてみます。
実際の詳細なリファレンスはこちらのページになるようです。各列に指定するカラムのタイプについてはこちらに一覧で用意されています。
事前準備
Google Apps ScriptでSheets APIを利用する場合には事前準備が必要です。以下の手順で追加します。
- スクリプトエディタの画面にて、左サイドのサービスの＋をクリックする
- Google Sheets APIを選択して追加をクリックする
これで、SheetsにつなげてAPIリクエストを送ることが可能になります。事前準備はこれだけ。あとはサンプルのデータなどを用意しておいて、対象の範囲を元にテーブル化を行います。
図：APIの追加をする必要があります。
ソースコード
範囲をテーブル化する
テーブル化したい範囲を明確に数値で指定して、なおかつタイトル行をカッチリ指定して範囲をテーブル化することが可能です。ただしこの時、tableIdを指定しておいたほうが後で削除やデータの追加時に必要になるので、便利です。
カラムに対して前述にあるようにColumnTypeを指定して細かく型の指定や、ドロップダウン化まで含めて指定が可能です。
//テーブルのID
var tableId = "japancar"; //テーブルIDを任意に指定しておく
//新規に範囲をテーブル化する
function setNewTable(){
//このファイルのIDを取得
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName("シート1");
const sheetid = sheet.getSheetId();
const ssid = spreadsheet.getId();
// 交互の背景色を削除
const bandings = sheet.getBandings();
bandings.forEach(banding => {
banding.remove();
});
//シートに書き出しオプション
let request = {
requests: [
{
addTable: {
table: {
tableId: tableId,
range: {
sheetId: sheetid,
startRowIndex: 0, //A1の行から
endRowIndex: sheet.getLastRow(), //最終行まで
startColumnIndex: 0, //A列から
endColumnIndex: sheet.getLastColumn() //G列まで
},
columnProperties: [
{
columnIndex: 0,
columnName: "メーカー",
columnType: "TEXT"
},
{
columnIndex: 1,
columnName: "車種",
columnType: "TEXT"
},
{
columnIndex: 2,
columnName: "車体色",
columnType: "TEXT"
},
{
columnIndex: 3,
columnName: "発売年",
columnType: "DOUBLE"
},
{
columnIndex: 4,
columnName: "エンジン型式",
columnType: "TEXT"
},
{
columnIndex: 5,
columnName: "車両価格 (JPY)",
columnType: "CURRENCY"
},
{
columnIndex: 6,
columnName: "備考",
columnType: "TEXT"
},
]
}
}
}
]
};
//テーブル化を実行する
let response = Sheets.Spreadsheets.batchUpdate(request, ssid);
console.log(response)
}
図：無事にテーブル化まで出来ました。
tableIdをリストアップする
作成したテーブルには表面上見えないtableIdというものがあります。コレがないと後でテーブルに対してデータの追加やテーブルの削除ができないので、前述で任意に指定しています。このtableIdをスプシ全体からリストアップするためのコードです。
//tableIdをリストアップする
function listBasicTableIds() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const spreadsheetId = spreadsheet.getId();
// シートのプロパティ（タイトル）と、シート内の基本テーブル情報（tableId）を取得するフィールド指定
const fieldsString = "sheets(properties(sheetId,title),tables(tableId))";
try {
// スプレッドシートのメタデータを取得
const spreadsheetData = Sheets.Spreadsheets.get(spreadsheetId, {
fields: fieldsString
});
if (!spreadsheetData.sheets) {
console.log('シートが見つかりません。');
return;
}
//発見フラグをオフにする
let foundTable = false;
// 各シートをループ
spreadsheetData.sheets.forEach(sheet => {
//シート内のtableを探索する
if (sheet.tables && Array.isArray(sheet.tables) && sheet.tables.length > 0) {
// シート内の各テーブルをループ
sheet.tables.forEach(table => {
//シート名を表示
const sheetTitle = sheet.properties ? sheet.properties.title : `(タイトル不明: ID ${sheet.properties.sheetId})`;
console.log(`${sheetTitle}`)
//テーブルIDを表示
if (table.tableId) {
console.log(`テーブルID:${table.tableId}`);
//発見フラグを立てる
foundTable = true;
}
});
}
});
if (!foundTable) {
console.log('スプシ内にテーブルは見つかりませんでした。');
}
} catch (e) {
console.log(`エラーが発生しました: ${e}`);
}
}
図：シート1にセットしたtableIdを無事取得できた
テーブルを削除する
指定のテーブルIDを元にテーブルを削除するためのコードです。ただし、テーブルデータもろとも消えるので注意
//テーブルを削除する
function deleteTable(){
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const spreadsheetId = spreadsheet.getId();
// 削除するテーブルの ID を指定
const targetid = tableId;
// deleteTable リクエストの作成
const request = {
requests: [
{
deleteTable: {
tableId: targetid
}
}
]
};
// batchUpdate メソッドでリクエストを送信
Sheets.Spreadsheets.batchUpdate(request, spreadsheetId);
}
テーブルに１行データを追加する
指定のテーブルIDを元に１行データを追加するためのコードです。
//テーブルに1行データを追加する
function appendRowToTable() {
//スプシを取得する
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const spreadsheetId = spreadsheet.getId();
// テーブルが存在するシート名を指定してください
const sheetName = "シート1";
const sheet = spreadsheet.getSheetByName(sheetName);
//シートIDを取得する
if (!sheet) {
console.log(`シート "${sheetName}" が見つかりません。`);
return;
}
const sheetId = sheet.getSheetId();
//テーブル用に1行データを追加する
const newRowData = [
{ userEnteredValue: { stringValue: "トヨタ" } }, // メーカー (TEXT)
{ userEnteredValue: { stringValue: "カローラ" } }, // 車種 (TEXT)
{ userEnteredValue: { stringValue: "白" } }, // 車体色 (TEXT)
{ userEnteredValue: { numberValue: 2024 } }, // 発売年 (DOUBLE -> numberValue)
{ userEnteredValue: { stringValue: "M20A-FKS" } }, // エンジン型式 (TEXT)
{ userEnteredValue: { numberValue: 2500000 } }, // 車両価格 (CURRENCY -> numberValue)
{ userEnteredValue: { stringValue: "新しいモデル" } } // 備考 (TEXT)
];
//リクエストを作成
const requests = [
{
appendCells: {
sheetId: sheetId,
tableId: tableId,
rows: [
{
values: newRowData
}
],
fields: "*"
}
}
];
const requestBody = {
requests: requests
};
try {
// batchUpdate メソッドでリクエストを送信
const response = Sheets.Spreadsheets.batchUpdate(requestBody, spreadsheetId);
console.log(`テーブル "${tableId}" へのデータ追加リクエストが成功しました。`);
} catch (e) {
// エラーハンドリング
console.log(`エラーが発生しました: ${e}`);
}
}
Internal error encountered.となったコード
コード自体は正しいと思うのだけれども、何故かInternal error encounteredとなって止まってしまったコードです。まだ出たばかりのAPIであるためなのか？それともリクエストがオカシイのか、、、
このトラブルが出た場合はおおむねサーバー側の問題なので、あまり沼にハマらないように。
図：何故かエラーが出て止まる
