Googleスプレッドシート^{(Google Sheets)}は、MicrosoftExcelで実行できるほぼすべてのことを実行できる強力なクラウドベースのスプレッドシートツールです。しかし、 Googleスプレッドシート^{(Google Sheets)}の真の力は、それに付属するGoogleScripting機能です。^{(Google Scripting)}

Google Appsスクリプトは、 ^{(Google Apps)}Googleスプレッドシートだけで^{(in Google Sheets)}なく、Googleドキュメント、Gmail、Googleアナリティクス^{(Google Analytics)}、およびその他のほぼすべてのGoogleクラウドサービスでも機能するバックグラウンドスクリプトツールです。これにより、これらの個々のアプリを自動化し、それらの各アプリを相互に統合できます。

この記事では、Google Apps Scriptingの使用を開始する方法、セルデータを読み書きするためのGoogle Sheetsでの基本的なスクリプトの作成、および最も効果的な高度なGoogleSheetsスクリプト機能について学習します。

GoogleAppsScriptを作成する方法^{(How to Create a Google Apps Script)}

今すぐ、 Googleスプレッドシート内から最初の^{(Google Sheets)}GoogleAppsスクリプトの作成を開始できます。 

これを行うには、メニューから[ツール]、[^(Tools)スクリプトエディター^{(Script Editor)}]の順に選択します。

これにより、スクリプトエディタウィンドウが開き、デフォルトでmyfunction（）という関数になります。ここで、 GoogleScript^{(Google Script)}を作成してテストできます。

試してみるには、あるセルからデータを読み取り、そのセルで計算を実行し、データ量を別のセルに出力するGoogleスプレッドシートスクリプト関数を作成してみてください。^{(Google Sheets)}

セルからデータを取得する関数は、getRange（ ）関数とgetValue（）関数です。行と列でセルを識別できます。したがって、行2と列1（A列）に値がある場合、スクリプトの最初の部分は次のようになります。

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

これにより、そのセルの値がデータ^(data)変数に格納されます。データに対して計算を実行してから、そのデータを別のセルに書き込むことができます。したがって、この関数の最後の部分は次のようになります。

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

関数の記述が完了したら、保存するディスクアイコンを選択します。 

このような新しいGoogleスプレッドシート^{(Google Sheets)}スクリプト関数を（実行アイコンを選択して）初めて実行するときは、スクリプトをGoogleアカウントで実行するための^{(Google Account)}承認^{(Authorization)}を提供する必要があります。

続行する権限を許可します。スクリプトを実行すると、スクリプトが計算結果をターゲットセルに書き込んだことがわかります。

基本的なGoogleAppsScript^{(Google Apps)}関数の記述方法がわかったところで、さらに高度な関数を見てみましょう。

getValuesを使用して配列をロードする^{(Use getValues To Load Arrays)}

配列を使用して、スクリプトを使用してスプレッドシート内のデータを計算するという概念を新しいレベルに引き上げることができます。getValuesを使用してGoogleApps^{(Google Apps)}スクリプトに変数を読み込む場合、変数はシートから複数の値を読み込むことができる配列になります。

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

データ変数は、シートのすべてのデータを保持する多次元配列です。データに対して計算を実行するには、forループを使用します。forループのカウンターは各行で機能し、データをプルする列に基づいて列は一定のままです。

このスプレッドシートの例では、次のように3行のデータに対して計算を実行できます。

for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

上記と同じように、このスクリプトを保存^(Save)して実行します。すべての結果がスプレッドシートの列2に入力されていることがわかります。

配列変数のセルと行を参照することは、getRange関数を使用する場合とは異なることに気付くでしょう。 

data [i] [0]は、最初の次元が行で2番目の次元が列である配列次元を参照します。これらは両方ともゼロから始まります。

getRange(i+1, 2)は、i = 1の場合（行1がヘッダーであるため）、2番目の行を参照し、2は結果が格納される2番目の列です。

appendRowを使用して結果を書き込む^{(Use appendRow To Write Results)}

新しい列ではなく新しい行にデータを書き込みたいスプレッドシートがある場合はどうなりますか？

これは、 appendRow^(appendRow)関数を使用して簡単に実行できます。この関数は、シート内の既存のデータを気にすることはありません。既存のシートに新しい行を追加するだけです。

例として、1から10までカウントし、[カウンター^(Counter)]列に2の倍数のカウンターを表示する関数を作成します。

この関数は次のようになります。

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

この関数を実行したときの結果は次のとおりです。

URLFetchAppを使用してRSSフィードを処理する^{(Process RSS Feeds With URLFetchApp)}

以前のGoogleスプレッドシート^{(Google Sheets)}スクリプト関数とURLFetchAppを組み合わせて、任意のWebサイトからRSSフィードを取得し、そのWebサイトに最近公開されたすべての記事のスプレッドシートに行を書き込むことができます。

これは基本的に、独自のRSSフィードリーダースプレッドシートを作成するためのDIYの方法です。^(DIY)

これを行うためのスクリプトもそれほど複雑ではありません。

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

ご覧のとおり、Xml.parseは^(Xml.parse)RSSフィードから各アイテムを引き出し、各行をタイトル、リンク、日付、説明に分けています。 

appendRow関数を使用すると、 RSSフィード内のすべてのアイテムの適切な列にこれらのアイテムを配置できます。

シートの出力は次のようになります。

RSSフィードのURLをスクリプトに埋め込む代わりに、シートにURLを含むフィールドを作成し、監視するWebサイトごとに1つずつ、複数のシートを作成することができます。

文字列を連結^{(Concatenate Strings)}し、キャリッジリターン^{(Carriage Return)}を追加します^(Add)

いくつかのテキスト操作機能を追加することでRSSスプレッドシートをさらに一歩進め、次に電子メール機能を使用して、サイトのRSSフィード内のすべての新しい投稿の要約を含む電子メールを自分に送信できます。

これを行うには、前のセクションで作成したスクリプトの下に、スプレッドシート内のすべての情報を抽出するスクリプトを追加する必要があります。 

RSSデータをスプレッドシート に書き込むために使用したのと同じ「items」配列からのすべての情報を一緒に解析することにより、件名と電子メールのテキスト本文を作成する必要があります。

これを行うには、「items」のForループの前に次の行を配置して、件名とメッセージを初期化します。

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

次に、「items」forループの最後（appendRow関数の直後）に次の行を追加します。

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

「+」記号は、4つの項目すべてを連結し、その後に「」が続き、各行の後にキャリッジリターンが表示されます。各タイトルデータブロックの最後に、適切にフォーマットされた電子メール本文に対して2つのキャリッジリターンが必要になります。

すべての行が処理されると、「body」変数は電子メールメッセージ文字列全体を保持します。これで、メールを送信する準備が整いました。

GoogleAppsScriptでメールを送信する方法^{(How To Send Email In Google Apps Script)}

Google Scriptの次のセクションでは、「件名」と「本文」をメールで送信します。GoogleScriptでこれを行うのは非常に簡単です。^{(Google Script)}

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

MailAppは、 ^(MailApp)Google Appsスクリプト内の非常に便利なクラスであり、Googleアカウントのメールサービスにアクセスしてメールを送受信できます。このおかげで、sendEmail関数を使用した1行で、メールアドレス、件名、本文だけで任意のメールを送信できます。^{(send any email)}

結果の電子メールは次のようになります。 

WebサイトのRSSフィードを抽出し、それを^(RSS)Googleスプレッドシート^{(Google Sheet)}に保存し、URLリンクを含めて自分自身に送信する機能を組み合わせると、任意のWebサイトの最新コンテンツをフォローするのに非常に便利です。

これは、アクションを自動化し、複数のクラウドサービスを統合するためにGoogleAppsスクリプトで利用できる機能の一例にすぎません。^{(Google Apps)}

5 Google Sheets Script Functions You Need to Know

Google Sheets is a powerful cloud-based spreadsheet tool that lets you do nearly everything yоu could do in Microsoft Excel. But the real power of Goоgle Sheets is the Google Scripting feature that comes with it.

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

In this article you’ll learn how to get started with Google Apps scripting, creating a basic script in Google Sheets to read and write cell data, and the most effective advanced Google Sheets script functions.

How to Create a Google Apps Script

You can get started right now creating your first Google Apps script from inside Google Sheets. 

To do this, select Tools from the menu, then Script Editor.

This opens the script editor window and defaults to a function called myfunction(). This is where you can create and test your Google Script.

To give it a shot, try creating a Google Sheets script function that will read data from one cell, perform a calculation on it, and output the data amount to another cell.

The function to get data from a cell is the getRange() and getValue() functions. You can identify the cell by row and column. So if you have a value in row 2 and column 1 (the A column), the first part of your script will look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var row = 2;
   var col = 1;
   var data = sheet.getRange(row, col).getValue();
}

This stores the value from that cell in the data variable. You can perform a calculation on the data, and then write that data to another cell. So the last part of this function will be:

   var results = data * 100;
   sheet.getRange(row, col+1).setValue(results);
}

When you’re done writing your function, select the disk icon to save. 

The first time you run a new Google Sheets script function like this (by selecting the run icon), you’ll need to provide Authorization for the script to run on your Google Account.

Allow permissions to continue. Once your script runs, you’ll see that the script wrote the calculation results to the target cell.

Now that you know how to write a basic Google Apps script function, let’s take a look at some more advanced functions.

Use getValues To Load Arrays

You can take the concept of doing calculations on data in your spreadsheet with scripting to a new level by using arrays. If you load a variable in Google Apps script using getValues, the variable will be an array that can load multiple values from the sheet.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var data = sheet.getDataRange().getValues();

The data variable is a multi-dimensional array that holds all of the data from the sheet. To perform a calculation on the data, you use a for loop. The counter of the for loop will work through each row, and the column remains constant, based on the column where you want to pull the data.

In our example spreadsheet, you can perform calculations on the three rows of data as follows.

for (var i = 1; i < data.length; i++) {
   var result = data[i][0] * 100;
   sheet.getRange(i+1, 2).setValue(result); 
   }
}

Save and run this script just as you did above. You’ll see that all of the results are filled into column 2 in your spreadsheet.

You’ll notice that referencing a cell and row in an array variable is different than with a getRange function. 

data[i][0] refers to the array dimensions where the first dimension is the row and the second is the column. Both of these start at zero.

getRange(i+1, 2) refers to the second row when i=1 (since row 1 is the header), and 2 is the second column where the results are stored.

Use appendRow To Write Results

What if you have a spreadsheet where you want to write data into a new row instead of a new column?

This is easy to do with the appendRow function. This function won’t bother any existing data in the sheet. It’ll just append a new row to the existing sheet.

As an example, make a function that will count from 1 to 10 and show a counter with multiples of 2 in a Counter column.

This function would look like this:

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();

   for (var i = 1; i<11; i++) {
      var result = i * 2;
     sheet.appendRow([i,result]);
   }
}

Here are the results when you run this function.

Process RSS Feeds With URLFetchApp

You could combine the previous Google Sheets script function and the URLFetchApp to pull the RSS feed from any website, and write a row to a spreadsheet for every article recently published to that website.

This is basically a DIY method to create your own RSS feed reader spreadsheet!

The script to do this isn’t too complicated either.

function myFunction() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var item, date, title, link, desc; 
   var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
   var doc = Xml.parse(txt, false);  

   title = doc.getElement().getElement("channel").getElement("title").getText();
   var items = doc.getElement().getElement("channel").getElements("item");   

// Parsing single items in the RSS Feed

for (var i in items) {
   item  = items[i];
   title = item.getElement("title").getText();
   link  = item.getElement("link").getText();
   date  = item.getElement("pubDate").getText();
   desc  = item.getElement("description").getText();
   
   sheet.appendRow([title,link,date,desc]);
}
}

As you can see, Xml.parse pulls each item out of the RSS feed and separates each line into the title, link, date and description. 

Using the appendRow function, you can put these items into appropriate columns for every single item in the RSS feed.

The output in your sheet will look something like this:

Instead of embedding the RSS feed URL into the script, you could have a field in your sheet with the URL, and then have multiple sheets – one for every website you want to monitor.

Concatenate Strings and Add a Carriage Return

You could take the RSS spreadsheet a step further by adding some text manipulation functions, and then use email functions to send yourself an email with a summary of all new posts in the site’s RSS feed.

To do this, under the script you created in the previous section, you’ll want to add some scripting that will extract all of the information in the spreadsheet. 

You’ll want to build the subject line and the email text body by parsing together all of the information from the same “items” array that you used to write the RSS data to the spreadsheet. 

To do this, initialize the subject and message by placing the following lines before the “items” For loop.

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

Then, at the end of the “items” for loop (right after the appendRow function), add the following line.

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

The “+” symbol  will concatenate all four items together followed by “\n” for a carriage return after each line. At the end of each title data block, you’ll want two carriage returns for a nicely formatted email body.

Once all rows are processed, the “body” variable holds the entire email message string. Now you’re ready to send the email!

How To Send Email In Google Apps Script

The next section of your Google Script will be to send the “subject” and the “body” via email. Doing this in Google Script is very easy.

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

The MailApp is a very convenient class inside of Google Apps scripts that gives you access to your Google Account’s email service to send or receive emails. Thanks to this, the single line with the sendEmail function lets you send any email with just the email address, subject line, and body text.

This is what the resulting email will look like. 

Combining the ability to extract a website’s RSS feed, store it in a Google Sheet, and send it to yourself with URL links included, makes it very convenient to follow the latest content for any website.

This is just one example of the power that’s available in Google Apps scripts to automate actions and integrate multiple cloud services.

Related posts

• ExcelをGoogleスプレッドシートに変換する4つの方法

• GoogleスプレッドシートとMicrosoftExcel–違いは何ですか？

• Googleドキュメントでテーブルの境界線を削除する方法

• Googleドキュメントチャットがドキュメントでの共同作業にどのように役立つか

• WordとGoogleドキュメントに透かしを挿入する方法

• プロファイルの読み込みでスタックするOutlookを修正する方法

• Excelのスピークセル機能の使用方法

• Microsoft Outlookが開きませんか？修正する10の方法

• Wordでマクロを作成して実行する方法

• Googleフォームを経費トラッカーとして使用する方法

• Wordでブックマークが定義されていないエラーを修正する方法

• Excelで空白行を削除する方法

• Microsoft Teamsとは何ですか？どのように機能しますか？

• ExcelのWhat-If分析の使用方法

• Excelで行を修正する方法

• DOCXファイルを開くときの終了タグ開始タグの不一致エラーを修正

• Wordの文の途中に箇条書きを挿入する

• MicrosoftWordで単語数を表示する方法

• MicrosoftOfficeでPDFドキュメントを作成する方法

• MSOfficeの「ライセンスを確認できません」というエラーメッセージを修正する