ONLYOFFICE’s diary

OSSドキュメントエディタの使い方やヒントをご紹介します

ONLYOFFICEマクロでGoogle検索結果をスプレッドシートに挿入する方法

Web検索は、日常的に行っているので、私たちの生活に欠かせないものとなっています。しかし、検索結果をどこかに保存する必要がある場合はどうでしょうか？例えばスプレッドシートに？これは、データを操作するのにとても便利な方法です。このブログ記事では、Google検索の結果をスプレッドシートに挿入するONLYOFFICEマクロを作成する方法をご紹介します。

https://www.onlyoffice.com/blog/wp-content/uploads/2022/07/Illyustratsiya_bez_nazvaniya_1658923937.png

Googleウェブ検索APIの選び方

APIとはApplication Programming Interfaceの略で、最近のインターネットの根幹をなすものです。どこにでもあります。天気予報のアプリであれ、携帯のメッセンジャーであれ。APIは、ウェブアプリケーションがサーバーと通信するために使用するプロトコルです。Google検索の結果を取得するにもAPIが必要なのは言うまでもありません。

利便性を考えると、Rapid APIプラットフォームを通じてGoogle Web Search APIを取得することをお勧めします。Rapid APIは、100万人以上の開発者が利用しているマーケットプレイスで、数千のAPIを発見し接続することができます。無料、フリーミアム、有料のプランがあります。すべてのAPIにはコードスニペットが付属しており、多くの時間を節約することができます。

Rapid APIへの登録が完了したら、Google Web Search APIを探しましょう。このとき、いくつかのオプションが利用可能です。

https://www.onlyoffice.com/blog/wp-content/uploads/2022/07/google-Search-1-1536x864.png

マクロをテストするために、このAPIを選びました。この API は月 300 リクエストのフリーミアムプランを提供しています。このプランで、マクロの機能を確認するには十分でしょう。もっと多くのリクエストが必要な場合は、別の API を選択するか、有料プランに加入すればよいでしょう。

注意! 各APIはユニークなアクセスキーを提供します。あなたのマクロは、あなたが契約したAPIでのみ動作してください。

APIパラメータの設定

APIページでは、検索リクエストに適用可能なパラメータを確認することができます。ここでは、Rapid API、Request URL、Header Parametersはそのままにしておきます。これは、APIがサーバーとやり取りするために必要なものです。Required Parametersには検索要求が含まれている。ここでは、"ONLYOFFICE editors "と入れています。

https://www.onlyoffice.com/blog/wp-content/uploads/2022/07/2022-07-27_172643.png

また、リクエストにいくつかのオプションのパラメータを追加することができます。この例では、取得したいページ、結果の数（最大値は100、デフォルト値は20）、検索リクエストのロケール、時間フィルタ、そして国を指定できます。これらのパラメータはデフォルトのままにしています。したがって、検索されるデータには、時間フィルタが適用されていない最初のページから始まる、英語の20件の結果が含まれることになります。

https://www.onlyoffice.com/blog/wp-content/uploads/2022/07/Googel-Search-3.png

次に、APIリクエストを行う言語を選択する必要があります。ここでは、JavaScriptでXML/HTTPリクエストを作成します。そこで、右側のパネルにあるコードスニペットの中から、JavaScript > XML/HttpRequestを選択します。

https://www.onlyoffice.com/blog/wp-content/uploads/2022/07/Code-snopet-Seb-Search.png

マクロに進む前に、どんなデータが戻ってくるか見てみましょう。このページでそれを行うことができます。そのためには、Test Endpoint ボタンをクリックする必要があります。結果は、右側のパネルに表示されます。

https://www.onlyoffice.com/blog/wp-content/uploads/2022/07/Results-web-search-response.png

APIリクエストでは、2つのアイテムが返されました。ここで注目すべきなのは結果配列です。これには、これからスプレッドシートに貼り付ける予定の検索結果が含まれています。よく見てみると、すべての検索結果配列には title、link、snippet の各要素が含まれていることがわかります。後ほど、titleとlinkの要素のみを取得し、スプレッドシートに挿入する予定です。しかし、ここではCode Snippetsに戻って、XML/HTTPリクエストをコピーします。

1列に2行を挿入する

このマクロのバリエーションでは、タイトルとリンクの値を組み合わせて、一緒に1つのカラムに挿入します。

そこで、ONLYOFICE Editors を開き、新しいマクロを作成し、上でコピーした XML/HTTP リクエストを貼り付けます。readstatechangeイベントの後に始まるif文に行き、console.logメソッドのある行を削除します。これは必要ありません。ここに私たち自身のコードを追加します。

まず、リクエストから取得したデータを JSON 形式に変換する必要があります。そのために、JSON.parse メソッドを使用して、その結果を oData 変数に格納します。

(function()
{
   const data = null;

  const xhr = new XMLHttpRequest();
  xhr.withCredentials = true;

  xhr.addEventListener("readystatechange", function () {
    if (this.readyState === this.DONE) {
      const oData = JSON.parse(xhr.responseText);

そして、データのフィルタリングを開始します。まず、結果の配列を取得し、別の変数に格納します。

const { results } =  oData;

これで、この配列の title と link の要素にアクセスできるようになりました。次のステップでは、mapメソッドを使用してタイトルとリンクの値を結合し、dataComb変数に格納します。こうすることで、それらが1つの行を形成し、後でスプレッドシートのセルに挿入されることになります。

 const dataComb = results.map((item) => ({
      title: item.title,
      link: item.url,
    }));

そこで、要素を含む変数dataCombを用意します。各要素には、タイトルとリンクの値が含まれています。私たちの目標は、それらをセルに挿入することです。そのために、forループを作成します。変数内の各要素を繰り返し処理し、文字列に変換して、対応するセルに挿入します。

 let nRow = 0;
      for (let i = 0; i < dataComb.length; i++) {
       const text = JSON.stringify(dataComb[i]); 
        var oWorksheet = Api.GetActiveSheet();
  oWorksheet.GetRangeByNumber(nRow, 0).SetValue(text);
  oWorksheet.GetRangeByNumber(nRow, 0).AutoFit(false, true);
  nRow++;
      }

まず、nRow変数を作成しました。その後、GetRangeByNumberメソッドでインデックスとして使用し、行を選択するようにしました。nRow変数をインクリメントして、繰り返しごとに次の行に切り替えています。dataComb変数の各要素を繰り返し、文字列に変換し、SetValueメソッドで選択された行に値を挿入しています。そのために、パラメータで宣言したi変数を取り出し、インデックスとして使用しました。また、AutoFitメソッドでセルの大きさを調整しています。

マクロの全コードは以下の通りです。

(function()
{
   const data = null;

  const xhr = new XMLHttpRequest();
  xhr.withCredentials = true;

  xhr.addEventListener("readystatechange", function () {
    if (this.readyState === this.DONE) {
      const oData = JSON.parse(xhr.responseText);
      const { results } =  oData;
      const dataComb = results.map((item) => ({
      title: item.title,
      link: item.url,
    }));
    let nRow = 0;
      for (let i = 0; i < dataComb.length; i++) {
       const text = JSON.stringify(dataComb[i]); 
        var oWorksheet = Api.GetActiveSheet();
  oWorksheet.GetRangeByNumber(nRow, 0).SetValue(text);
  oWorksheet.GetRangeByNumber(nRow, 0).AutoFit(false, true);
  nRow++;
      }
    }
  });

  xhr.open("GET", "https://google-search26.p.rapidapi.com/search?q=Onlyoffice%20editors&hl=en&tbs=qdr%3Aa");
  xhr.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b");
  xhr.setRequestHeader("X-RapidAPI-Host", "google-search26.p.rapidapi.com");

  xhr.send(data);
})();

それでは、マクロを実行し、その動きを見てみましょう。

https://www.onlyoffice.com/blog/wp-content/uploads/2022/07/Google-search6-1536x864.png

行を別々のカラムに挿入する

このマクロのバリエーションでは、タイトルとリンクの値を2つの別々の列に挿入します。

そのために、mapメソッドを使って、タイトルとリンクの値を2つの変数に保存します。

 const { results } =  oData;
    
      const title = results.map((item) => item.title);
      const link = results.map((item) => item.link);

ここで、2つのforループを作成します。1つはtitle変数を繰り返し処理し、その値を最初の列に挿入します。

   let nRowTitle = 0;
      for (let i = 0; i < title.length; i++) {
       const text = JSON.stringify(title[i]); 
          var oWorksheet = Api.GetActiveSheet();
  oWorksheet.GetRangeByNumber(nRowTitle, 0).SetValue(text);
  oWorksheet.GetRangeByNumber(nRowTitle, 0).AutoFit(false, true);
  nRowTitle++;
      }

もう一つは、リンク変数を反復処理し、その値を2列目に挿入します。

 let nRowLink = 0;
       for (let i = 0; i < link.length; i++) {
       const text = JSON.stringify(link[i]); 
  oWorksheet.GetRangeByNumber(nRowLink, 1).SetValue(text);
  oWorksheet.GetRangeByNumber(nRowLink, 1).AutoFit(false, true);
  nRowLink++;
       }

GetRangeByNumberメソッドの第2パラメータを1に変更したことに注意してください。 このパラメータは、列の位置に対応しています。こうすることで、リンクの値が2列目に挿入されるようになります。

マクロの全コードは以下の通りです。

(function()
{
    const data = null;

  const xhr = new XMLHttpRequest();
  xhr.withCredentials = true;

  xhr.addEventListener("readystatechange", function () {
    if (this.readyState === this.DONE) {
      const oData = JSON.parse(xhr.responseText);
      const { results } =  oData;
      const title = results.map((item) => item.title);
      const link = results.map((item) => item.link);
    let nRowTitle = 0;
      for (let i = 0; i < title.length; i++) {
       const text = JSON.stringify(title[i]); 
          var oWorksheet = Api.GetActiveSheet();
  oWorksheet.GetRangeByNumber(nRowTitle, 0).SetValue(text);
  oWorksheet.GetRangeByNumber(nRowTitle, 0).AutoFit(false, true);
  nRowTitle++;
      }
     let nRowLink = 0;
       for (let i = 0; i < link.length; i++) {
       const text = JSON.stringify(link[i]); 
  oWorksheet.GetRangeByNumber(nRowLink, 1).SetValue(text);
  oWorksheet.GetRangeByNumber(nRowLink, 1).AutoFit(false, true);
  nRowLink++;
       }
    }
  });

xhr.open("GET", "https://google-search26.p.rapidapi.com/search?q=Onlyoffice%20editors&hl=en&tbs=qdr%3Aa");
xhr.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b");
xhr.setRequestHeader("X-RapidAPI-Host", "google-search26.p.rapidapi.com");

xhr.send(data);
})();

では、マクロを実行してみましょう。

https://www.onlyoffice.com/blog/wp-content/uploads/2022/07/google-Search-7-1536x864.png

このマクロが、ルーチンワークの自動化やデータの構造化をより便利にする一助となれば幸いです。ONLYOFFICEのマクロでできることのほんの一部に過ぎません。

ONLYOFFICEのマクロはJavaScriptベースですので、非常に汎用性が高く、お客様のニーズに合わせて簡単にカスタマイズすることができます。私たちは、あなたが独自のマクロを実験し、作成することを奨励します。また、あなたのアイデアやマクロを私たちと共有することを躊躇しないでください。私たちは、議論と協力にオープンです。あなたの探求的な努力に幸あれ！