「JavaScriptとGASが同じものという認識がなくハードルが高く見える」
「マクロは使えるけどスプレッドシートの操作は未経験で興味がある」
「スプレッドシートを使ってGASを勉強し始めたけどgetRangeやsetValueが何をしているかわからなくて先に進めていない」
業務効率化や自動化の代表といえばGoogle Apps Script（GAS）によるスプレッドシートの操作です。
GASはビジネスプロセスを自動化し、データの処理を効率化する上で非常に重要です。
今回の記事では、GASを使用してスプレッドシートを操作する基本的な手法に焦点を当てます。SpreadSheetApp、getRange、setValue、insertSheet、getActiveSpreadSheetといった要素を駆使して、どのようにしてスプレッドシートのセルに値を記載したり取得するかについて解説していきましょう。
また動画もあるので一緒に活用してくださいね。
GASでスプレッドシートを操作する基本メソッド
まずGASにはスプレッドシートを操作する便利なクラスがあります。
SpreadSheetAppというクラスがあり、こちらをインスタンス化することでセルの操作やシートの操作ができる便利なメソッドがたくさん使えるようになります。
これらはJavaScriptベースで書かれるのですが通常のWebで使うJavaScriptでは使えないGAS専用の文法になります。
スプレッドシートを新規で作成してヘッダーメニューの「拡張機能」から「AppScript」をクリックしてエディタを立ち上げ、以下のコードを実行してみます。
初回のみ承認作業がありますのでポップアップをクリックしていきます。
「権限を確認」をクリックします。
ログインしているGmailアカウントが表示されますのでクリックします。
左下の「詳細」をクリックします。
こちらも左下の「〜〜に移動」をクリックします。
こちらは「許可」をクリックします。
そうするとコードの実行が再開されます。
ちなみにこれらの作業は初回のみで2回目からは表示されません。
コードの解説に戻ります。
function createCalendar() {
const sheet = SpreadsheetApp.getActiveSpreadsheet();
// 一番左にして新規作成
const newSheet = sheet.insertSheet(0);
}
SpreadSheetAppというクラスはインスタンス化するときにgetActiveSpreadSheetを実行することで、現在のシートをアクティブにすることになります。
スプレッドシートには複数のシートを作ることができますので、「どのシートを操作するのか」ということを認識しておくことが重要になります。
最初は「シート1」のみなので「シート1」がアクティブになります。
この状態でinsertSheet(0)とすると新規でシートを作成します。
新規でシートを作成すると追加された「シート2」の方にアクティブが移行して、これから書くコードは「シート2で実行する」という認識になりますので注意してください。
GASでスプレッドシートの名前を変更する方法
今作ったシートに名前を付けてみたいと思います、シート2ではなく本日の日付を名前にするには以下のようにします。
function createCalendar() {
// ここを追加
const today = new Date();
const month = today.getMonth();
const year = today.getFullYear();
const calendar = getCalendar(year, month);
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const newSheet = sheet.insertSheet(0);
// ここを追加
newSheet.setName(`${year}年${month + 1}月`);
}
日付の取得には通常のJavaScriptで使えるDateクラスを使用しました。
GASではSpreadSheetAppのようなGAS専用の文法と、通常のJavaScriptで使える文法と混在させることができます。
ちなみにgetMonthメソッドで「月」を取得できるのですが、こちらは1月(0) ~ 12月(11)というようなインデックス番号になっているのでプラス１する必要があります。
シート名の変更にはSpreadSheetAppのメソッドであるsetNameを使用します。
setNameは引数に入れたものがシートの名前として変更されます。
ちなみに上図のようにシートが3枚になった状態で日付が入ったはずです。
GASはコードを実行する度に毎回0からスタートするので何回も実行すると、その分だけシートがどんどん生成されていく仕組みです。
開発時点では問題ないですが完成して運用する際には不要なシートは削除してから実行するようにしましょう。
また現状では「2023年12月」というシートがあるので、もう一度コードを実行するとシート名が被ってエラーになります。
試しにもう一度「実行」をクリックしてみます。
名前のないシートであれば使わないシートがひたすら生成されていくだけですが、今回のように名前をつけると2回実行できなくなります。
このようなケースでは1回実行する度にシートを削除する事になります。
GASでセルの値を一括変更する方法
まずはセルの値を変更することをやったみたいと思います。
1個くらいであれば手動で変更すれば良いですが複数件あると手動で変更するのが大変ですし、変更内容に法則性があるのであればGASの得意分野です。
例えば以下のようなシートがあったとします。
A列のIDについては連番で入力されるものとして、「自分たちが使いやすい表記方法」に変更したい場合があります。
アンケートフォームやメール、ECサイトなど何かしらデータを送る画面の中でIDは連番か自動生成、もしくはユーザー入力になります。
データを受ける側にとって都合の良い表記にできないケースに、データを受けた後のスプレッドシート上で変更することになります。
上記の例だと連番になっているのを「student_〇〇」のような表記にしたいとします。
まずは値を一括変更するセルの範囲をgetRangeメソッドで指定します。
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
mySheet = ss.getSheetByName("students");
// セルの範囲を指定
let queryDataRange = mySheet.getRange(2,1,mySheet.getLastRow() - 1,1);
}getRangeメソッドではセルの範囲を引数4つで指定します。
第一引数に開始行、第二引数に開始列、第三引数に終了行、第四引数に終了列です。
今回のシートの場合、1行目はヘッダーなので対象外となりますので第一引数には2行目ということで「2」を入れて、A列なので「1」を入れることで「A2セル」が開始位置になります。
終了位置ですがデータによって行数が変動します。
getLastRowというメソッドを使うことで実行時の行数をカウントしてくれます。
先ほどのヘッダー1行が対象外のためgetLastRow() – 1とすることになります。
終了列もA列なので第四引数に「1」を入れることで、データ数によって終了位置がA10セルまでだったり、A20までなど変動させることになります。
範囲指定ができたので現在の値をgetCell(行、列).getValue()で取得して、値の変更をsetValue(変更内容)というメソッドで行います。
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
mySheet = ss.getSheetByName("students");
let queryDataRange = mySheet.getRange(2,1,mySheet.getLastRow() - 1,1);
// ここを追加
for(let i = 1; i <= mySheet.getLastRow() - 1; i++){
let value = queryDataRange.getCell(i,1).getValue();
console.log(value);
queryDataRange.getCell(i,1).setValue(`students_${value}`);
}
}上記のコードでは現在のセルの値を変数valueに一度格納しています。
コンソールログでvalueを確認すると以下のようにA2からA〇までの値が出力されます。
続いてsetValueの変更内容についてはテンプレートリテラルを使ってstudent_〇〇にしています。
スプレッドシートを確認すると変更されているのがわかります。
GASでスプレッドシートのセルにカレンダーの日付と曜日を入力する方法
今回はよくあるカレンダーを入力してみます。
今月分の日付と曜日をA列とB列に一覧にして順番に入力したいのですが、カレンダー情報は先に自分で作っておく必要があります。
またプログラミングやエクセルのマクロを触ったことがある人はわかると思いますが、通常の日付ではなく日本語対応した表示には自作が必要です。
function createCalendar() {
const today = new Date();
const month = today.getMonth();
const year = today.getFullYear();
const calendar = getCalendar(year, month);
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const newSheet = sheet.insertSheet(0);
newSheet.setName(`${year}年${month + 1}月`);
}
// ここを追加
const DAYS_OF_WEEK = ["日", "月", "火", "水", "木", "金", "土"];
function getCalendar(year, month) {
const daysInMonth = new Date(year, month + 1, 0).getDate();
const calendar = [];
for (let i = 1; i <= daysInMonth; i++) {
const date = `${year}/${month + 1}/${i}`;
const dayOfWeek = new Date(date).getDay();
calendar.push([date, DAYS_OF_WEEK[dayOfWeek]]);
}
return calendar;
}カレンダーを作成する関数をgetCalendarとして別で作成してみました。
また曜日については定数DAYS_OF_WEEKという配列で日本語の文字列で準備しています。
日付の取得は先ほども使ったJavaScriptのDateクラスを使って、取得した日付情報を日本語対応した定数calendarという配列に変換させるものです。
月の最終日はDateクラスのgetDateメソッドを実行すれば31日になったり30日になったりします。
さらに曜日情報はDateクラスのgetDayメソッドで取得できます。
あとはgetDayではmonday,thuesdayのような英語表記になっているのを、DAY_OF_WEEKで作成したものに繰り返し処理で変換していきます。
こちらの関数getCalendarを元の関数myFunctionで実行して、セルに入力する情報を持ってきます。
関数getCalendarには引数として「年」「月」の2つの引数を渡すようにしていますので、関数myFunctionで実行するときは定数yearと定数monthが使用できます。
function createCalendar() {
const today = new Date();
const month = today.getMonth();
const year = today.getFullYear();
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const newSheet = sheet.insertSheet(0);
newSheet.setName(`${year}年${month + 1}月`);
// ここを追加
const calendar = getCalendar(year, month);
console.log(calendar);
}
const DAYS_OF_WEEK = ["日", "月", "火", "水", "木", "金", "土"];
function getCalendar(year, month) {
const daysInMonth = new Date(year, month + 1, 0).getDate();
const calendar = [];
for (let i = 1; i <= daysInMonth; i++) {
const date = `${year}/${month + 1}/${i}`;
const dayOfWeek = new Date(date).getDay();
calendar.push([date, DAYS_OF_WEEK[dayOfWeek]]);
}
return calendar;
}
関数getCalendarでは定数calendarという新しい配列にカレンダー情報を変換していました。
関数myFunctionではgetCalendarを実行すると定数calendarの中身のカレンダー情報を取得できます。
あとは定数calendarの値をセルに入力するだけです。
カレンダー情報はJavaScriptのDateクラスを使っていましたが、セルの入力では再度GASのSpreadSheetAppのメソッドを使って以下のように書きます。
function createCalendar() {
const today = new Date();
const month = today.getMonth();
const year = today.getFullYear();
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const newSheet = sheet.insertSheet(0);
newSheet.setName(`${year}年${month + 1}月`);
const calendar = getCalendar(year, month);
console.log(calendar);
// ここを追加
const endRow = calendar.length;
const range = newSheet.getRange(`A1:B${endRow}`);
range.setValues(calendar);
}
const DAYS_OF_WEEK = ["日", "月", "火", "水", "木", "金", "土"];
function getCalendar(year, month) {
const daysInMonth = new Date(year, month + 1, 0).getDate();
const calendar = [];
for (let i = 1; i <= daysInMonth; i++) {
const date = `${year}/${month + 1}/${i}`;
const dayOfWeek = new Date(date).getDay();
calendar.push([date, DAYS_OF_WEEK[dayOfWeek]]);
}
return calendar;
}まず入力する場所(範囲)はgetRangeというメソッドの引数にセル範囲を指定します。
「”A1:B1″」のように固定の値もOKですが、今回のような月によってデータ量が変わるものは変数にした方が良さそうです。
GASではJavaScriptで使うテンプレートリテラル「`${ }`」で変数と文字列を同時に使用できます。
先に定数calendarの要素数、つまり日付の数を取得して新しく定数endRowに代入しておきます。
あとはgetRange(`A1:B${endRow}`)とすれば「”A1:B30″」になったり「”A1:B31″」になるわけです。
getRangeで指定したセル範囲にはsetValuesというメソッドで値を書き込めます。
書き込む値である配列calendarを引数に入れてあげるだけです。
これくらいなら手動のドラッグアンドドロップで作れますが、月によって最終日を変えたりシートを自分で立ち上げたりする事に比べるとプログラムで実行した方が楽ですね。
また定期的に実行させる作業があれば、さらに便利さが増えていきます。
GASからセルのデザインを変える方法
データの入力ができることがわかったところで、セルの見た目を変更する方法も紹介しておきます。
GASを使っている個人、会社の多くで「データ入力まではGASがやってセルの見た目は手動で設定している」というケースをよく見かけます。
実はGASを使うとセルの大きさ、文字の種類などデザインに関することも変更できます。
どうせなら色んな作業を自動化できたほうが良いのでセルのデザインについても練習しておきましょう。
スプレッドシートは前章のカレンダーをそのまま使っていきます。
以下のようにするとセルの見た目が変わります。
function createCalendar() {
const today = new Date();
const month = today.getMonth();
const year = today.getFullYear();
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const newSheet = sheet.insertSheet(0);
newSheet.setName(`${year}年${month + 1}月`);
const calendar = getCalendar(year, month);
console.log(calendar);
const endRow = calendar.length;
const range = newSheet.getRange(`A1:B${endRow}`);
range.setValues(calendar);
// ここから追加
range.setFontSize(12);
range.setVerticalAlignment("middle");
range.setHorizontalAlignment("center");
range.setBackground("#BED754");
range.setFontFamily('Kosugi Maru');
newSheet.setRowHeights(1, endRow, 50);
}
〜〜省略〜〜
データの入力が終わった後にセルのデザインまで変えることができていますね。
今やったのは以下のとおりです。
setFontSize(12)：フォトサイズを12ptに設定
setVerticalAlignment(“middle”)：文字の縦配置を中央に設定
setHorizontalAlignment(“center”)：文字の横配置を中央に設定
setBackground(“#BED754”)：セルの背景色を設定
setFontFamily(‘Kosugi Maru’)：文字の種類を設定
setRowHeights(1, endRow, 50)：セルの高さを設定
スプレッドシートのヘッダーメニューにあるアイコンをクリックするなどで手動で設定できるものは、プログラムでも対応することができます。
データの羅列だけでなくデザインまで整えてあげることで資料っぽい感じになりますね。
スプレッドシートを別のスプレッドシートにコピーする
続いてスプレッドシートにある値を別のスプレッドシートにコピーする方法です。
コピー元とコピー先の2つのスプレッドシートがあるとして、それぞれのスプレッドシートURLを取得します。
スプレッドシートを開いた時のURLで「https://docs.google.com/spreadsheets/d/xxxxxxxxxx」となっているURLの中にIDが含まれているためです。
コピー元とコピー先の2つのURLを調べた上でコードは以下のようにします。
function myFunction() {
// コピー元
const fromFileSheet = SpreadsheetApp.openByUrl("https://xxxxxxx").getSheetByName("students");
// コピー先
const toFile = SpreadsheetApp.openByUrl("https://xxxxxx");
const newFileSheet = fromFileSheet.copyTo(toFile).setName("students");
toFile.setActiveSheet(newFileSheet);
toFile.moveActiveSheet(1);
}SpreadsheetApp.openByUrlとすると先ほど説明したスプレッドシートのURLを参考にスプレッドシートを取得します。
またコピー元についてはgetSheetByNameメソッドでシート名を文字列で指定しておきます。
コピー元のシートに対してcopyToメソッドを実行して、引数にコピー先のシートを入れるとコピペが完了します。
また併せてsetNameメソッドを実行することで引数にコピー先のシート名を設定することができます。
今回はコピー元と同じ「students」をsetNameの引数に入れて同じシート名になるようにしました。
さらにsetActiveSheetを実行した後に、moveActiveSheet(1)とするとコピー先で「students」シートが先頭に移動させることができます。
基本的にシートは新しく追加したり今回のようにコピペするとシートの順番は後ろになるようになってい流ためです。
複数のスプレッドシートを操作することはGAS作業では必須のスキルになりますので練習しておきましょう。
GASでシートのソートを行う方法
他にもシートの中でソートを行う方法もよく使われます。
ソートにおいても他の操作と同じくセルの範囲指定がポイントになります。
データが含まれる行と列を漏れなく範囲に入れないとデータが崩れてしまいますので注意しましょう。
今回は以下のようなシートについてC列の年齢順にしたいと思います。
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
mySheet = ss.getSheetByName("students");
let queryDataRange = mySheet.getRange(2,1,mySheet.getLastRow() - 1,mySheet.getLastColumn());
queryDataRange.sort({column: 2, ascending: true})
}今回はセルのデータがある範囲ということでgetRangeメソッドの終了位置は行がgetLastRow() – 1で、列がgetLastColumn()になります。
ソートについてはsortというメソッドがありオブジェクト形式で設定を書きます。
columnプロパティには「どの列を基準に並べ替えるか」という列の指定になります。
今回はC列なので「2」を入れました。
またascendingというのが昇順と降順でtrueかfalseで選択します。
ascendingをfalseに変更して実行してみると逆順になります。
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
mySheet = ss.getSheetByName("students");
let queryDataRange = mySheet.getRange(2,1,mySheet.getLastRow() - 1,mySheet.getLastColumn());
// ここを変更
queryDataRange.sort({column: 2, ascending: false})
}