マクロを使ってYouTubeの検索結果をスプレッドシートに挿入する方法

YouTubeの検索結果を<a href="https://www.onlyoffice.com/ja/desktop.aspx?utm_source=qiita&amp;utm_medium=article&amp;utm_id=youtube_search_results_macro" target="_blank" rel="noopener">ONLYOFFICEスプレッドシート</a>に統合するマクロです。YouTube検索機能を組み込むことで、スプレッドシートをマルチメディアコンテンツで充実させる便利なソリューションを提供します。マクロの作り方を見ていきましょう。

<img class="img-popup aligncenter wp-image-76542" src="https://static-blog.onlyoffice.com/wp-content/uploads/2023/11/21122253/youtube-macro.png" alt="A Macro that inserts Youtube Search Results in ONLYOFFICE Spreadhseet" width="736" height="372" />

<!--more-->
<h2><strong>APIについて</strong></h2>
このマクロでは、<a href="https://rapidapi.com/" target="_blank" rel="noopener">RapidAPI</a>のパワーを統合して、データ検索プロセスを容易にします。XMLHttpRequest機能を使ってターゲットURLを定義し、RapidAPIが提供するコードスニペットを使ってヘッダーを設定します。<a href="https://rapidapi.com/marindelija/api/youtube-search-results/" target="_blank" rel="noopener">Marin Delija氏のYouTube Search Results APIリファレンス</a>を使用することにしました。ベーシックプランが無料で、毎日100リクエストまで許可されるので理想的な選択です。さらに、リクエストを増やす必要がある場合は、有料プランも利用できます。すべての人のニーズと予算のバランスがとれた実用的な選択です。

既にRapidAPIコミュニティの一員であれば、上記のリンクをクリックし、APIリファレンスに登録するだけです。新規ユーザの場合、サインアッププロセスは簡単です - メールアドレスを入力するだけです！
<h2><strong>マクロの構築</strong></h2>
<pre class="theme:github font:monospace lang:js decode:true ">  const data = null;
  const oWorksheet = Api.GetActiveSheet();
  let query = []; // empty query array to store the queries
</pre>
これらの行はONLYOFFICEスプレッドシートのアクティブシートを取得し、変数oWorksheetに代入します。また、検索されるクエリを格納する空の配列queryを初期化しています。
<pre class="theme:github font:monospace lang:js decode:true ">// Takes the queries from the cells A1, A2, and A3.
  for (let i = 1; i &lt; 4; i++) {
    const value = String(oWorksheet.GetRange("A" + i).GetValue());
    if (value !== null &amp;&amp; value.length !== 0) {
      query.push(value);
    }
  }
</pre>
この for ループはセル A1 から A3 をループし、値を取得します。値が存在し、かつ空でなければ、その値がクエリ配列に追加されます。
<pre class="theme:github font:monospace lang:js decode:true">  // polpulates the cells with the titles and links from the queries
  function populate(count, title, link) {
    let nRowTitle = 9;
    for (let j = 0; j &lt; title.length; j++) {
      let passedTitleCount = count * 3;
      const text = JSON.stringify(title[j]);
      const textWithoutQuotes = text.replace(/"/g, "");
      oWorksheet.GetRangeByNumber(nRowTitle - 1, passedTitleCount).SetValue(query[count]);
      oWorksheet.GetRangeByNumber(nRowTitle - 1, passedTitleCount).SetAlignHorizontal("center");
      oWorksheet
        .GetRangeByNumber(nRowTitle + j, passedTitleCount)
        .SetValue(textWithoutQuotes);
      oWorksheet
        .GetRangeByNumber(nRowTitle + j, passedTitleCount)
        .AutoFit(false, true);
    }
    let nRowLink = 9;
    for (let h = 0; h &lt; link.length; h++) {
      let passedLinkCount = count * 3 + 1;
      const text = JSON.stringify(link[h]);
      const textWithoutQuotes = text.replace(/"/g, "");
      oWorksheet
        .GetRangeByNumber(nRowLink + h, passedLinkCount)
        .SetValue(textWithoutQuotes);
      oWorksheet
        .GetRangeByNumber(nRowLink + h, passedLinkCount)
        .AutoFit(false, true);
    }
  }
</pre>
populate関数は最初は圧倒されるように見えるかもしれませんが、複雑ではありません。populate関数はcount、title、linkの3つのパラメータをとります。カウントに基づいて、スプレッドシート内の適切なセルに結果を入力します。この関数には2つのforループがあります; 1つはタイトルの入力を助け、もう1つのforループはタイトルと並んだリンクの入力を助けます。
<pre class="theme:github font:monospace lang:js decode:true">// refreshes the values in all the cells of the spreadsheet
  function reloadCellValues() {
    let reload = setTimeout(function () {
      Api.asc_calculate(Asc.c_oAscCalculateType.All);
    }, 5000);
  }
</pre>
このパートでは、setTimeout を使用してスプレッドシート内のすべてのセル値を 5 秒遅れて更新する関数 reloadCellValues を定義します。
<pre class="theme:github font:monospace lang:js decode:true ">for (let count = 0; count &lt; query.length; count++) {
    const url = `https://youtube-search-results.p.rapidapi.com/youtube-search/?q=${query[count]}`;
    const xhr = new XMLHttpRequest();
    xhr.onload = function () {
      const apiData = JSON.parse(this.response);
      const { videos: results } = apiData;
      const title = results.map((i) =&gt; i.title);
      const link = results.map((i) =&gt; i.link);
      populate(count, title, link);
    };
</pre>
ここでは、for ループを使用して、query 配列に格納された各クエリを繰り返し処理し、RapidAPI にリクエストします。動的な URL を url 変数に定義します。定数 xhr は XMLHttpRequest オブジェクトで初期化されます。

xhr.onload で、XMLHttpRequest が正常にリクエストを完了したときのイベント ハンドラを定義します。

onload 関数内でデータを取得し、必要な値を抽出するためにオブジェクトを再構築します。

次に、map メソッドを使用してすべてのタイトルとリンクを繰り返し処理し、2 つの配列に格納します。これらの配列はカウントと共に populate 関数に渡されます。
<pre class="theme:github font:monospace lang:js decode:true "> // XMLHttpRequest Code Snippets copied from RapidAPI
    xhr.open("GET", url);
    xhr.setRequestHeader(
      "X-RapidAPI-Key",
      YourApiKey
    );
    xhr.setRequestHeader(
      "X-RapidAPI-Host",
      "youtube-search-results.p.rapidapi.com"
    );
    xhr.send(data);
  }
</pre>
次に、XMLHttpRequest のターゲット URL を定義し、リクエストのヘッダを設定します。このコードセグメントは、RapidAPIが提供するXMLHttpRequestのコードスニペットから抜粋したものです。「YourApiKey」を個人のAPIキーに置き換える必要があります。
<pre class="theme:github font:monospace lang:js decode:true ">reloadCellValues();
</pre>
最終的には、先ほど定義した reloadCellValues メソッドを呼び出します。
<h2>マクロの全コード</h2>
マクロのコード全体は以下のとおりです。
<pre class="theme:github font:monospace lang:js decode:true ">(function () {
  const data = null;

  const oWorksheet = Api.GetActiveSheet();

  let query = []; // empty query array to store the queries

  // Takes the queries from the cells A1, A2, and A3.
  for (let i = 1; i &lt; 4; i++) {
    const value = String(oWorksheet.GetRange("A" + i).GetValue());
    if (value !== null &amp;&amp; value.length !== 0) {
      query.push(value);
    }
  }

  // polpulates the cells with the titles and links from the queries
  function populate(count, title, link) {
    let nRowTitle = 9;
    for (let j = 0; j &lt; title.length; j++) {
      let passedTitleCount = count * 3;
      const text = JSON.stringify(title[j]);
      const textWithoutQuotes = text.replace(/"/g, "");
      oWorksheet
        .GetRangeByNumber(nRowTitle - 1, passedTitleCount)
        .SetValue(query[count]);
      oWorksheet
        .GetRangeByNumber(nRowTitle - 1, passedTitleCount)
        .SetAlignHorizontal("center");
      oWorksheet
        .GetRangeByNumber(nRowTitle + j, passedTitleCount)
        .SetValue(textWithoutQuotes);
      oWorksheet
        .GetRangeByNumber(nRowTitle + j, passedTitleCount)
        .AutoFit(false, true);
    }

    let nRowLink = 9;
    for (let h = 0; h &lt; link.length; h++) {
      let passedLinkCount = count * 3 + 1;
      const text = JSON.stringify(link[h]);
      const textWithoutQuotes = text.replace(/"/g, "");
      oWorksheet
        .GetRangeByNumber(nRowLink + h, passedLinkCount)
        .SetValue(textWithoutQuotes);
      oWorksheet
        .GetRangeByNumber(nRowLink + h, passedLinkCount)
        .AutoFit(false, true);
    }
  }

  // refreshes the values in all the cells of the spreadsheet
  function reloadCellValues() {
    let reload = setTimeout(function () {
      Api.asc_calculate(Asc.c_oAscCalculateType.All);
    }, 5000);
  }

  // for loop for multiple queries
  for (let count = 0; count &lt; query.length; count++) {
    const url = `https://youtube-search-results.p.rapidapi.com/youtube-search/?q=${query[count]}`;
    const xhr = new XMLHttpRequest();
    xhr.onload = function () {
      const apiData = JSON.parse(this.response);
      const { videos: results } = apiData;
      const title = results.map((i) =&gt; i.title);
      const link = results.map((i) =&gt; i.link);
      populate(count, title, link);
    };

    // XMLHttpRequest Code Snippets copied from RapidAPI
    xhr.open("GET", url);
    xhr.setRequestHeader("X-RapidAPI-Key", "YOUR API KEY GOES HERE");
    xhr.setRequestHeader(
      "X-RapidAPI-Host",
      "youtube-search-results.p.rapidapi.com"
    );
    xhr.send(data);
  }

  reloadCellValues();
})();
</pre>
マクロの動きを見てみましょう。
<div style="text-align: center;"><iframe width="560" height="315" src="https://www.youtube.com/embed/VzmZU8NX1tw?si=i7xEnI1Hyjaye_zS" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen="allowfullscreen" data-mce-fragment="1"></iframe></div>
YouTubeの検索結果データをあなたのスプレッドシートに直接シームレスに統合するマクロがお役に立てれば幸いです。

ONLYOFFICE APIのパワーを活用するチャンスをお見逃しなく。私たちのAPIメソッドの豊富なライブラリは、あなたのアイデアを現実に変える鍵です。ご質問や革新的なコンセプトがありましたら、ぜひ<a href="https://www.onlyoffice.com/ja/contacts.aspx?utm_source=qiita&amp;utm_medium=article+&amp;utm_id=youtube_search_results_macro" target="_blank" rel="noopener">私たちと共有してください</a>。
<div class="useful-links">
<h3>お役立ちリンク</h3>
<a href="https://www.youtube.com/playlist?list=PLCF48HEKMOYNBygGjr9lSw3PLpVXQ6s_t" target="_blank" rel="noopener">ONLYOFFICE Docsのマクロ【無料講座】</a>

<a href="https://api.onlyoffice.com/plugin/macrosamples/?utm_source=qiita&amp;utm_medium=article+&amp;utm_id=youtube_search_results_macro" target="_blank" rel="noopener"><u>マクロのサンプル</u></a>

<a href="https://github.com/ONLYOFFICE" target="_blank" rel="noopener"><u>ONLYOFFICEのGitHubページ</u></a>

<a href="https://api.onlyoffice.com/docbuilder/spreadsheetapi?utm_source=qiita&amp;utm_medium=article+&amp;utm_id=youtube_search_results_macro" target="_blank" rel="noopener"><u>ONLYOFFICE Spreadsheet API</u></a>

</div>