How to insert YouTube search results into your spreadsheet using macros

This macro integrates YouTube search results into ONLYOFFICE Spreadsheet Editor. By embedding YouTube search functionality, it offers a convenient solution for enriching spreadsheets with multimedia content. Let’s see how to build the macro.

About the API

For this macro, we are integrating the power of RapidAPI to ease our data retrieval process. Using the XMLHttpRequest feature, we define the target URL and set headers using the code snippets provided by RapidAPI. We’ve decided to go with Marin Delija’s YouTube Search Results API reference. It is an ideal choice because the basic plan is free, giving us the green light for 100 requests each day. Plus, there are paid plans available if we need to ramp up your requests. It’s a practical choice that balances everyone’s needs and budget!.

If you’re already a part of the RapidAPI community, simply hit the link above and subscribe to the API reference. For new users, the signup process is simple — just provide your email address, and you’re good to go!

Building the macro

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

These lines retrieve the active sheet in the ONLYOFFICE Spreadsheet and assign it to the variable oWorksheet. We also initialize an empty array query, which will store the queries to be searched.

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

This for loop loops through cells A1 to A3, grabbing the values. If a value exists and is not empty, it gets appended to the query array.

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

The populate function might look overwhelming at first, but it is not complicated. The populate function takes three parameters: count, title, and link. Based on the count, it populates the appropriate cells in the spreadsheet with the results. It has two for loops; one helps populate the titles, while the other for loop populates the links alongside the titles.

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

In this part, we define a function reloadCellValues that uses setTimeout to refresh all cell values in the spreadsheet after a 5-second delay.

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

Here, we use a for loop to iterate over each query stored in the query array, making subsequent requests to RapidAPI. We define a dynamic URL in the url variable. A constant xhr is initialised with the XMLHttpRequest object.

With xhr.onload, we define an event handler for when the XMLHttpRequest has successfully completed its request.

Within the onload function, we retrieve data and then destructure the object to extract the necessary values.

Then, we use the map method to iterate through all titles and links, storing them in two arrays. These arrays, along with the count, are then passed to the populate function.

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

Next, we define the target URL for the XMLHttpRequest and configure the headers for the request. This code segment is extracted from the provided code snippets by RapidAPI for an XMLHttpRequest. You will need to replace “YourApiKey” with your personal API key, which can be found in RapidAPI.

reloadCellValues();

Ultimately, we invoke the reloadCellValues method we defined earlier.

The full macro code

Here is the entire code of the macro.

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

Let’s see the macro in action.

We hope that the macro has proven to be useful, seamlessly integrating YouTube search results data directly into your spreadsheet.

Don’t miss the chance to harness the power of the ONLYOFFICE API. Our extensive library of API methods is your key to transforming your ideas into reality. If you have any questions or innovative concepts, we encourage you to share them with us. Your input is highly valued, and we are excited about the possibility of collaborating with you. Best of luck in your exploratory endeavors!