LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@ONLYOFFICE

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

Posted at

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 &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 < 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メソッドの豊富なライブラリは、あなたのアイデアを現実に変える鍵です。ご質問や革新的なコンセプトがありましたら、ぜひ私たちと共有してください。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?