Spreadsheet API: ApiRange.Find is not a function

Document Builder
,
1

Do you want to: Report a bug
DocumentBuilder version: online demo / desktop 7.3.3.50 (inside a plugin, using callCommand )
Installation method:
OS: windows 10

Unable to execute example from the docs ONLYOFFICE Api Documentation - Find in the online demo ONLYOFFICE Api Documentation - Overview

Or use inside a plugin with

      var oSheet = Api.GetActiveSheet();
      var oRange = oSheet.GetRange("A1:AAA1000");
      var oSearchRange = oRange.Find(
        "EXAMPLE",
        "A1",
        "xlValues",
        "xlWhole",
        "xlByColumns",
        "xlNext",
        true
      );

sdk-all.js:15863 TypeError: oRange.Find is not a function
at eval (eval at B (sdk-all.js:15863), :6:33)
at eval (eval at B (sdk-all.js:15863), :15:3)
at B (sdk-all.js:15863)

Am I doing something wrong or is this a bug?

2

No longer an issue in desktop app, after updating to 7.4.0.163

3

Hello @arc-grigorenko

I’m glad to hear that the issue is not reproducible after the update.

4

Hi! Unfortunately, I had some more problems with this function.

I tried to search for multiple values in a specified range (E10:AAA1000) and coloring them, using ApiRange.Find() and ApiRange.FindNext()

find
find694×1106 81.7 KB

  1. Only one cell being found in a column, with come columns resulting in no matches
  2. Cells outside of E10:AAA1000 being found

Code:

    window.Asc.plugin.callCommand(() => {
      var oSheet = Api.GetActiveSheet();
      var oRange = oSheet.GetRange("E10:AAA1000");
      var oSearchRange = oRange.Find(
        "EXAMPLE",
        "E10",
        "xlValues",
        "xlPart",
        "xlByColumns",
        "xlNext",
        true
      );

      oSearchRange.SetFillColor(Api.CreateColorFromRGB(255, 213, 191));
      
      /*
        I remember the cells Col and Row and stop the cycle after looping to previously 
        visited cell,  but looping infinitely or in a for loop for a set amount of iterations 
        results in the same behavior 
      */
      while (/*looping condition*/) {
        oSearchRange = oRange.FindNext(oSearchRange);
        oSearchRange.SetFillColor(Api.CreateColorFromRGB(255, 213, 191));
      }
});

I also tried to search with ‘xlByRows’, which resulted in similar behavior, but one cell was being found per row instead of column

5

Sorry for the late response.
Could you please provide complete example of the code? In particular, what looping condition you have used to achieve the result on the screenshot.

6

I remember the cells I’ve visited and stop if I encounter an already visited cell:

    window.Asc.plugin.callCommand(() => {
      const functionCells = [];
      var didLoop = false;

      var oSheet = Api.GetActiveSheet();
      var oRange = oSheet.GetRange("E10:AAA1000");
      var oSearchRange = oRange.Find(
        "EXAMPLE",
        "E10",
        "xlValues",
        "xlPart",
        "xlByColumns",
        "xlNext",
        true
      );

      if (oSearchRange) {
        functionCells.push({
          Col: oSearchRange.Col,
          Row: oSearchRange.Row,
        });
        oSearchRange.SetFillColor(Api.CreateColorFromRGB(255, 213, 191));
      }

      while (!didLoop && oSearchRange) {
        oSearchRange = oRange.FindNext(oSearchRange);
        const foundCell = {
          Col: oSearchRange.Col,
          Row: oSearchRange.Row,
        };

        if (
          functionCells.some(
            (cell) => cell.Col === foundCell.Col && cell.Row === foundCell.Row
          )
        ) {
          didLoop = true;
        } else {
          functionCells.push(foundCell);
          oSearchRange.SetFillColor(Api.CreateColorFromRGB(255, 213, 191));
        }
      }
    });

But using a simple for loop, with amount of iterations that larger, then amount of cells we’re trying to find results in the same behavior. If we log the cells while finding them, we’ll see that the findNext loops through the same cells infinitely. I don’t think the condition has anything to do with it

    window.Asc.plugin.callCommand(() => {
      var oSheet = Api.GetActiveSheet();
      var oRange = oSheet.GetRange("E10:AAA1000");
      var oSearchRange = oRange.Find(
        "EXAMPLE",
        "E10",
        "xlValues",
        "xlPart",
        "xlByColumns",
        "xlNext",
        true
      );

      oSearchRange.SetFillColor(Api.CreateColorFromRGB(255, 213, 191));

      for (let i = 0; i < 200; i++) {
        oSearchRange = oRange.FindNext(oSearchRange);
        oSearchRange.SetFillColor(Api.CreateColorFromRGB(255, 213, 191));
      }
    });
7

Sorry for the late response.
Thank you for the code. We will take a closer look at it. I’ll let you know about the results.