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

2023年11月22日著者：Denis

YouTubeの検索結果をONLYOFFICEスプレッドシートに統合するマクロです。YouTube検索機能を組み込むことで、スプレッドシートをマルチメディアコンテンツで充実させる便利なソリューションを提供します。マクロの作り方を見ていきましょう。

A Macro that inserts Youtube Search Results in ONLYOFFICE Spreadhseet

APIについて

このマクロでは、RapidAPIのパワーを統合して、データ検索プロセスを容易にします。XMLHttpRequest機能を使ってターゲットURLを定義し、RapidAPIが提供するコードスニペットを使ってヘッダーを設定します。Marin Delija氏のYouTube Search Results APIリファレンスを使用することにしました。ベーシックプランが無料で、毎日100リクエストまで許可されるので理想的な選択です。さらに、リクエストを増やす必要がある場合は、有料プランも利用できます。すべての人のニーズと予算のバランスがとれた実用的な選択です。

既にRapidAPIコミュニティの一員であれば、上記のリンクをクリックし、APIリファレンスに登録するだけです。新規ユーザの場合、サインアッププロセスは簡単です – メールアドレスを入力するだけです！

マクロの構築

  const data = null;
  const oWorksheet = Api.GetActiveSheet();
  let query = []; // empty query array to store the queries

これらの行はONLYOFFICEスプレッドシートのアクティブシートを取得し、変数oWorksheetに代入します。また、検索されるクエリを格納する空の配列queryを初期化しています。

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

この for ループはセル A1 から A3 をループし、値を取得します。値が存在し、かつ空でなければ、その値がクエリ配列に追加されます。

  // polpulates the cells with the titles and links from the queries
  function populate(count, title, link) {
    let nRowTitle = 9;
    for (let j = 0; j < 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 < 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);
    }
  }

populate関数は最初は圧倒されるように見えるかもしれませんが、複雑ではありません。populate関数はcount、title、linkの3つのパラメータをとります。カウントに基づいて、スプレッドシート内の適切なセルに結果を入力します。この関数には2つのforループがあります; 1つはタイトルの入力を助け、もう1つのforループはタイトルと並んだリンクの入力を助けます。

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

このパートでは、setTimeout を使用してスプレッドシート内のすべてのセル値を 5 秒遅れて更新する関数 reloadCellValues を定義します。

for (let count = 0; count < 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) => i.title);
      const link = results.map((i) => i.link);
      populate(count, title, link);
    };

ここでは、for ループを使用して、query 配列に格納された各クエリを繰り返し処理し、RapidAPI にリクエストします。動的な URL を url 変数に定義します。定数 xhr は XMLHttpRequest オブジェクトで初期化されます。

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

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

次に、map メソッドを使用してすべてのタイトルとリンクを繰り返し処理し、2 つの配列に格納します。これらの配列はカウントと共に populate 関数に渡されます。

 // 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);
  }

次に、XMLHttpRequest のターゲット URL を定義し、リクエストのヘッダを設定します。このコードセグメントは、RapidAPIが提供するXMLHttpRequestのコードスニペットから抜粋したものです。「YourApiKey」を個人のAPIキーに置き換える必要があります。

reloadCellValues();

最終的には、先ほど定義した reloadCellValues メソッドを呼び出します。

マクロの全コード

マクロのコード全体は以下のとおりです。

(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 < 4; i++) {
    const value = String(oWorksheet.GetRange("A" + i).GetValue());
    if (value !== null && 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 < 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 < 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 < 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) => i.title);
      const link = results.map((i) => 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();
})();

マクロの動きを見てみましょう。

YouTubeの検索結果データをあなたのスプレッドシートに直接シームレスに統合するマクロがお役に立てれば幸いです。

ONLYOFFICE APIのパワーを活用するチャンスをお見逃しなく。私たちのAPIメソッドの豊富なライブラリは、あなたのアイデアを現実に変える鍵です。ご質問や革新的なコンセプトがありましたら、ぜひ私たちと共有してください。

お役立ちリンク

ONLYOFFICE Docsのマクロ【無料講座】

マクロのサンプル

ONLYOFFICEのGitHubページ

ONLYOFFICE Spreadsheet API

ONLYOFFICEの無料アカウントを登録する

オンラインでドキュメント、スプレッドシート、スライド、フォーム、PDFファイルの閲覧、編集、共同作業