こんにちは、WebエンジニアのShotaです。
ある業務でスプレッドシートで管理されているデータをDBへ移行する必要があり、GASを実装しました。
今回はGASを使ってシートのデータからSQLクエリを生成する処理をご紹介します！
GASとは
Google Apps ScriptとはGoogle Workspaceアプリと連携可能なアプリケーション開発プラットフォームです。
JavaScriptで記述可能で、スクリプトファイルの拡張子は「.gs」です。
インストール不要でブラウザにコードエディターが用意されているので、とても気軽に開発できます。
今回はスプレッドシートの表データを整形、別シートに出力する処理をGASで記述した例を紹介します！
前提
顧客データを管理する以下のスプレッドシートを例として考えます。
この表のデータをDBにInsertして登録したいときを想定します。
ただし曜日は数値を文字列に変換し、時刻は時間部分のみ取得します。
INSERT INTO extra_shift_rules (name, account_type, price, day, start_at, end_at) VALUES ( '木村雅人', '法人', 356000, '月', 6, 10 );
実装
「拡張機能」から「Apps Script」をクリックするとエディターを開けます。
↓
必要な処理は以下の通りです。
- sheet情報を取得する(getSheet)
データを取得元のシートや出力先のシートの呼び出しに使います。 - 時刻から時間を取得する処理(formatHour)
8:00のようなデータを8と取得します。 - 顧客データシートから整形したデータを取得する処理(getData)
表の全データを1行ずつ取得します。
getRange(`A${row}`)のように記述することでループさせて取得できます。 - InsertするSQLクエリを生成する処理(createInsertQuery)
取得元シート(顧客データ)・出力先シートを設定します。
顧客データの開始行や最終行を取得して設定します。
new Arrayで指定した長さの配列を生成します。
.forEach文のループではrowを顧客データの開始行に設定してループが適切に実行されるようにしています。
getDataを実行してデータ取得し、insertQueryで整形します。
出力先シートに出力します。
/**
* sheet情報を取得する処理
* @param {SpreadsheetApp.Sheet} sheetName
* @return {{ sheet: SpreadsheetApp.Sheet }}
*/
const getSheet = (sheetName) => {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName(sheetName);
return { sheet };
};
/**
* 時刻から時間を取得する処理
* @param {object} hourValue
* @returns {{
* hour: number;
* }}
*/
const formatHour = (hourValue) => {
const date = new Date(hourValue);
const hour = date.getHours();
return hour;
};
/**
* 顧客データシートから整形したデータを取得する処理
* @param {sheet} hourValue
* @returns {{
* name: string;
* accountType: string;
* price: number;
* day: string;
* startAt: number;
* endAt: number;
* }}
*/
const getData = (sheet, row) => {
const name = sheet.getRange(`B${row}`).getValue();
const accountType = sheet.getRange(`C${row}`).getValue();
const price = sheet.getRange(`D${row}`).getValue();
const dayValue = sheet.getRange(`E${row}`).getValue();
const dayMap = { 1: "月", 2: "火" };
const day = dayMap[dayValue];
const startAtValue = sheet.getRange(`F${row}`).getValue();
const startAt = formatHour(startAtValue);
const endAtValue = sheet.getRange(`G${row}`).getValue();
const endAt = formatHour(endAtValue);
return { name, accountType, price, day, startAt, endAt };
};
/**
* InsertするSQLクエリを生成する処理
*/
const createSingleInsertQuery = () => {
const { sheet } = getSheet("顧客データ");
const { sheet: targetSheet } = getSheet("[GAS]単発insertクエリ");
const lastRow = sheet
.getRange(sheet.getMaxRows(), 1)
.getNextDataCell(SpreadsheetApp.Direction.UP)
.getRow();
const firstRow = 5;
const roomsLengthOffset = 1;
new Array(lastRow - firstRow + roomsLengthOffset)
.fill("")
.forEach((_, index) => {
const row = index + firstRow;
const { name, accountType, price, day, startAt, endAt } = getData(
sheet,
row
);
const insertQuery = [
"INSERT INTO customer (name, account_type, price, day, start_at, end_at) VALUES (",
`'${name}',`,
`'${accountType}',`,
`${price},`,
`'${day}',`,
`${startAt},`,
`${endAt}`,
");",
].join(" ");
const targetCellOffset = 1;
const range = targetSheet.getRange(`A${index + targetCellOffset}`);
range.setValue(insertQuery);
});
};
実行する関数を選択し、実行をクリックします。
↓
実装した通りにInsertするSQLクエリが出力されました。
では実装にSQLを実行してみます。
↓
データ登録に成功しました、正しいSQLクエリが出力されたことが分かりました。
最後に
今回の実装ではスプレッドシートのデータからSQLクエリを生成しました。
出力部分を変更することで表データをJSONで出力したい時などにも役立ちます！
参考
スペースマーケットでは一緒にサービスを成長させていく仲間を探しています！
ご興味ある方はこちらからご応募お待ちしております！
スペースを簡単に貸し借りできるサービス「スペースマーケット」のエンジニアによる公式ブログです。 弊社採用技術スタックはこちら -> whatweuse.dev/company/spacemarket
Discussion