Jak przekazać zakres do funkcji niestandardowej w Arkuszach kalkulacyjnych Google?

Chcę stworzyć funkcję, która przyjmuje zakres ... coś takiego:

function myFunction(range) {
  var firstColumn = range.getColumn();
  // loop over the range
}

Komórka odwoływałaby się do niej za pomocą:

=myFunction(A1:A4)

Problem polega na tym, że gdy próbuję to zrobić, parametr wydaje się przekazywać tylko wartości do funkcji. Dlatego nie mogę użyć żadnej z metod Range, takich jak getColumn(). Gdy próbuję to zrobić, pojawia się następujący błąd:

error: TypeError: Nie można znaleźć funkcji getColumn w obiekcie 1,2,3.

Jak mogę wysłać rzeczywisty zakres, a nie tylko wartości do jednej z moich niestandardowych funkcji?

Więc długo i ciężko szukałem dobrej odpowiedzi na to pytanie i oto, co znalazłem:

1. niezmodyfikowany parametr zakresu przekazuje wartości komórek w zakresie, a nie sam zakres (jak wyjaśnił Gergely) np .: kiedy to zrobisz=myFunction(a1:a2)

2. aby użyć zakresu w funkcji, musisz najpierw przekazać zakres jako ciąg (np .:) =myFunction("a1:a2"), a następnie przekształcić go w zakres z następującym kodem wewnątrz funkcji:

   Function myFunction(pRange){
     var sheet = SpreadsheetApp.getActiveSpreadsheet();
     var range = sheet.getRange(pRange);
   }
   

3. Wreszcie, jeśli chcesz skorzystać z zalet przekazywania zakresu (np. Inteligentnego kopiowania odniesień zakresu do innych komórek) ORAZ chcesz przekazać go jako ciąg, musisz użyć domyślnej funkcji, która akceptuje zakres jako parametr i wyświetla wynik ciąg, którego możesz użyć. Opisuję oba sposoby, które znalazłem poniżej, ale wolę drugi.

   Dla wszystkich arkuszy kalkulacyjnych Google: =myFunction(ADDRESS(row(A1),COLUMN(A1),4)&":"&ADDRESS(row(A2),COLUMN(A2),4));

   W przypadku nowych Arkuszy Google: =myFunction(CELL("address",A1)&":"&CELL("address",A2));

rangejest traktowany jako tablica 2d javascript. Możesz uzyskać liczbę wierszy range.lengthi liczbę kolumn za pomocą range[0].length. Jeśli chcesz uzyskać wartość z wiersza ri kolumny cużycia: range[r][c].

Gdy przekazujesz a Rangedo funkcji arkusza kalkulacyjnego Google, środowisko wykonuje się paramRange.getValues()domyślnie, a twoja funkcja otrzymuje wartości w zakresie jako dwuwymiarową tablicę ciągów, liczb lub obiektów (jak Date). RangeObiekt nie jest przekazywana do niestandardowej funkcji arkusza kalkulacyjnego.

Poniższa TYPEOF()funkcja powie ci, jakie dane otrzymujesz jako parametr. Formuła

=TYPEOF(A1:A4)

wywoła skrypt w następujący sposób:

funkcja replaceCell () {
  var resultCell = SpreadsheetApp.getActiveSheet (). getActiveCell ();
  var paramRange = SpreadsheetApp.getActiveSheet (). getRange ('A1: A4');
  var paramValues ​​= paramRange.getValues ​​();

  var resultValue = TYPEOF (paramValues);

  resultCell.setValue (resultValue);
}

funkcja TYPEOF (wartość) {
  if (typeof value! == 'object')
    zwracany typ wartości;

  var details = '';
  if (wartość instanceof Array) {
    details + = '[' + value.length + ']';
    if (wartość [0] instanceof Array)
      szczegóły + = '[' + wartość [0]. długość + ']';
  }

  var nazwa_klasy = wartość.konstruktor.nazwa;
  return className + szczegóły;
}

Alternatywnie, zainspirowany komentarzem @Lipis, możesz wywołać swoją funkcję za pomocą współrzędnych wiersza / kolumny jako dodatkowych parametrów:

=myFunction(B1:C2; ROW(B1); COLUMN(B1); ROW(C2); COLUMN(C2))

W tej formie formułę można łatwo skopiować (lub przeciągnąć) do innych komórek, a odniesienia do komórek / zakresu zostaną automatycznie dostosowane.

Twoja funkcja skryptu musiałaby zostać zaktualizowana jako taka:

function myFunction(rangeValues, startRow, startColumn, endRow, endColumn) {
  var range = SpreadsheetApp.getActiveSheet().getRange(startRow, startColumn, endRow - startRow + 1, endColumn - startColumn + 1);
  return "Range: " + range.getA1Notation();
}

Należy zauważyć, że getRangefunkcja przyjmuje startRow, startColumni następnie liczbę wierszy i liczbę kolumn, - nie końca wiersza i koniec kolumny. Tak więc arytmetyka.

Można to zrobić . Odwołanie do przekazanego zakresu można uzyskać, analizując formułę w aktywnej komórce, która jest komórką zawierającą formułę. To powoduje założenie, że funkcja niestandardowa jest używana samodzielnie, a nie jako część bardziej złożonego wyrażenia: np . =myfunction(A1:C3)Nie =sqrt(4+myfunction(A1:C3)).

Ta funkcja zwraca indeks pierwszej kolumny z przekazanego zakresu.

function myfunction(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');
  }

  // everything so far was only range extraction
  // the specific logic of the function begins here

  var firstColumn = range.getColumn();  // or whatever you want to do with the range
  return firstColumn;
}

Rozszerzyłem doskonały pomysł w odpowiedzi user79865 , aby działał w większej liczbie przypadków i z wieloma argumentami przekazywanymi do funkcji niestandardowej.

Aby go użyć, skopiuj poniższy kod, a następnie w niestandardowym wywołaniu funkcji w GetParamRanges()ten sposób, przekazując mu nazwę funkcji:

function CustomFunc(ref1, ref2) {

  var ranges = GetParamRanges("CustomFunc"); // substitute your function name here

  // ranges[0] contains the range object for ref1 (or null)
  // ranges[1] contains the range object for ref2 (or null)

  ... do what you want

  return what_you_want;
}

Oto przykład, aby zwrócić kolor komórki:

/**
* Returns the background color of a cell
*
* @param {cell_ref} The address of the cell
* @return The color of the cell
* @customfunction
*/
function GetColor(ref) {

  return GetParamRanges("GetColor")[0].getBackground();
}

Oto kod i kilka dodatkowych wyjaśnień:

/**
* Returns an array of the range object(s) referenced by the parameters in a call to a custom function from a cell
* The array will have an entry for each parameter. If the parameter was a reference to a cell or range then 
* its array element will contain the corresponding range object, otherwise it will be null.
*
* Limitations:
* - A range is returned only if a parameter expression is a single reference.
*   For example,=CustomFunc(A1+A2) would not return a range.
* - The parameter expressions in the cell formula may not contain commas or brackets.
*   For example, =CustomFunc(A1:A3,ATAN2(4,3),B:E) would not parse correctly.
* - The custom function may not appear more than once in the cell formula.
* - Sheet names may not contain commas, quotes or closing brackets.
* - The cell formula may contain white space around the commas separating the custom function parameters, or after
*   the custom function name, but not elsewhere within the custom function invocation.
* - There may be other limitations.
* 
* Examples:
*   Cell formula: =CUSTOMFUNC($A$1)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: range object for cell A1 in the sheet containing the formula
*
*   Cell formula: =CUSTOMFUNC(3, 'Expenses'!B7)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: null
*                 ranges[1]: range object for cell B7 in sheet Expenses
* 
*   Cell formula: =sqrt(4+myfunction(A1:C3))
*   Usage:        var ranges = GetParamRanges("MyFunction");
*   Result:       ranges[0]: range object for cells A1 through C3 in the sheet containing the formula
*
*   Cell formula: =CustomFunc(A1+A2, A1, A2)
*   Usage:        var ranges = GetParamRanges("CustomFunc");
*   Result:       ranges[0]: null
*                 ranges[1]: range object for cell A1 in the sheet containing the formula
*                 ranges[2]: range object for cell A2 in the sheet containing the formula
*
* @param {funcName} The name of the custom function (string, case-insensitive)
* @return The range(s) referenced by the parameters to the call
*/
function GetParamRanges(funcName) {
  var ourSheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var re = new RegExp(".+" + funcName + "\\s*\\((.*?)\\)","i");
  var ranges=[]; // array of results

  try {
    var args = formula.match(re)[1].split(/\s*,\s*/) // arguments to custom function, separated by commas
    // if there are no args it fails and drops out here

    for (var i=0; i<args.length; i++) {
      var arg=args[i].split('!'); // see if arg has a sheet name
      try {
        if (arg.length == 1) { // if there's no sheet name then use the whole arg as the range definition
          ranges[i] = ourSheet.getRange(arg[0]);
        }
        else { // if there's a sheet name, use it (after removing quotes around it)
          var sheetName=arg[0].replace(/'/g, '');
          var otherSheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
          ranges[i] = otherSheet.getRange(arg[1]);
        }
      }
      catch(e) { // assume it couldn't be identified as a range for whatever reason
        ranges[i]=null;
      }
    }
  }
  catch(e) {}

  return ranges
}

Rozróżniam dwie różne funkcje niestandardowe w Arkuszu kalkulacyjnym Google za pomocą Google Apps Script:

1. Taki, który wymaga interfejsu API; SpreadsheetApp, ( przykład )
2. Taki, który nie wykonuje żadnych połączeń ( przykład )

Pierwsza funkcja jest w stanie zrobić prawie wszystko. Oprócz dzwonienia do usługi Arkusz kalkulacyjny, może ona także korzystać z GmailApp lub dowolnej usługi udostępnianej przez Google. Wywołania API spowolnią proces. Można przekazać lub pobrać zakres, aby uzyskać dostęp do wszystkich dostępnych metod.

Druga funkcja ogranicza się tylko do „Arkusza kalkulacyjnego”. Tutaj można skorzystać z JavaScript do przerobienia danych. Te funkcje są zwykle bardzo szybkie. Przekazany zakres to nic innego jak tablica 2D zawierająca wartości.

W swoim pytaniu zaczynasz od wywołania .getColumn()metody. To wyraźnie wskazuje, że potrzebujesz funkcji niestandardowej typu 1, jak opisano powyżej.

Zobacz następującą odpowiedź na temat ustawiania arkusza kalkulacyjnego i arkusza, aby utworzyć funkcję niestandardową.

Pracowałem nad tym kilka miesięcy temu i wymyśliłem bardzo prosty kludge: utwórz nowy arkusz z nazwą każdej komórki jako jej zawartością: Komórka A1 mogłaby wyglądać następująco:

= arrayformula(cell("address",a1:z500))

Nazwij arkusz „Ref”. Następnie, gdy potrzebujesz odwołania do komórki jako ciągu zamiast zawartości, użyj:

= some_new_function('Ref'!C45)

Oczywiście musisz sprawdzić, czy funkcja przechodzi przez łańcuch (jedną komórkę), czy tablicę 1D lub 2D. Jeśli otrzymasz tablicę, będzie ona miała wszystkie adresy komórek jako ciągi, ale od pierwszej komórki oraz szerokości i wysokości możesz dowiedzieć się, czego potrzebujesz.

Pracowałem nad tym przez cały ranek i widziałem dowody na to, co omawiają powyższe nie-odpowiedzi. Rozsądny i oboje chcemy ADRESU przekazanej komórki, a nie wartości. Odpowiedź jest bardzo łatwa. Nie da się tego zrobić.

Znalazłem kilka obejść, które polegają na ustaleniu, która komórka zawiera formułę. Trudno powiedzieć, czy pomogłoby to Senseful powyżej. Co ja robiłem?

The data.

     [A]      [B]       [C]       [D]     [E]     [F]       [H]
[1] Name      Wins     Losses    Shots   Points   Fouls   Most recent
                                                          WL Ratio
[2] Sophia     4         2         15      7       1         0
[3] Gloria     11        3         11      6       0         0
[4] Rene       2         0         4       0       0         0
[5] Sophia     7         4         18      9       1         1.5

Kolumna H to Sophia's (Wygrane - PrevWins) / (Przegrane - PrevLosses)

(7–4) / (4–2) = 1,5

Ale nie wiemy, w którym wierszu wcześniej pojawiła się Sophia.
Można to wszystko zrobić za pomocą WYSZUKAJ.PIONOWO, jeśli na stałe wpisujesz A jako kolumnę nazwy. Po WYSZUKAJ.PIONOWO otrzymywałem #NA (nazwa nie została znaleziona) i # DIV0 (mianownik zero) i otoczyłem go = JEŻELI (JEŻELI (...)), aby wyświetlić w tych warunkach bardziej smaczny tekst. Teraz miałem potwornie duży wyraz twarzy, który był nieporęczny i niemożliwy do utrzymania. Chciałem więc rozwinięcia makra (nie istnieje) lub funkcji niestandardowych.

Ale kiedy zrobiłem pomocnika SubtractPrevValue (komórkę), otrzymywał „7” zamiast B5. Nie ma wbudowanego sposobu pobierania obiektów komórki lub zakresu z przekazanych argumentów.
Jeśli zmuszę użytkownika do ręcznego wprowadzenia nazwy komórki w cudzysłowie, mogę to zrobić ... SubtractPrevValue („B5”). Ale to naprawdę kopiowanie / wklejanie ścięgien ścięgnistych i względne funkcje komórek.

Ale potem znalazłem obejście.

SpreadsheetApp.getActiveRange () JEST KOMÓRKĄ zawierającą formułę. To wszystko, co naprawdę musiałem wiedzieć. Numer wiersza. Poniższa funkcja pobiera NUMERIC numer kolumny i odejmuje poprzednie wystąpienie w tej kolumnie.

function SubtractPrevValue(colNum) 
{
  var curCell = SpreadsheetApp.getActiveRange();
  var curSheet = curCell.getSheet();
  var curRowIdx = curCell.getRowIndex();
  var name = curSheet.getRange(curRowIdx, 1).getValue();  // name to match
  var curVal =  curSheet.getRange(curRowIdx, colNum).getValue();

  var foundRowIdx = -1;
  for (var i=curRowIdx-1;i>1;i--)
  { 
    if (curSheet.getRange(i, 2).getValue() == name)
    {
      return curVal - curSheet.getRange(i, colNum).getValue();
    }
  }
  return curVal;  //default if no previous found
}

Ale potem odkryłem, że to naprawdę NAPRAWDĘ powolne. Jedna i dwie sekundy opóźnień, gdy wyświetla się „Myślenie ...” Więc wracam do masowo nieczytelnej, niemożliwej do utrzymania formuły arkusza.

Zamiast udostępnić obiekt „Zakres”, programiści Google przekazują prawie losowy typ danych. Opracowałem następujące makro, aby wydrukować wartość danych wejściowych.

function tp_detail(arg)
{
    var details = '';

    try
    {
        if(typeof arg == 'undefined')
           return details += 'empty';

        if (typeof arg !== 'object')
        {
            if (typeof arg == 'undefined')
                return details += 'empty';

            if (arg.map)
            {
                var rv = 'map: {';
                var count = 1;
                for (var a in arg)
                {
                    rv += '[' + count + '] ' + tp_detail(a);
                    count = count + 1;
                }
                rv += '}; '
                return rv;
            }
            return (typeof arg) + '(\'' + arg + '\')';
        }


        if (arg instanceof Array)
        {
            details += 'arr[' + arg.length + ']: {';
            for (var i = 0; i < arg.length; i++)
            {
                details += '[' + i + '] ' + tp_detail(arg[i]) + ';';
            }
            details += '} ';
        }
        else
        {

            var className = arg.constructor.name;
            return className + '(' + arg + ')';
        }
    }
    catch (e)
    {
        var details = '';
        details = 'err : ' + e;
    }


    return details;
}

Oto moja kompilacja poprzednich odpowiedzi

**
 *
 * @customfunction
 **/
function GFR(){
  var ar = SpreadsheetApp.getActiveRange();
  var as = SpreadsheetApp.getActive();
  return ar.getFormula()
    .replace(/=gfr\((.*?)\)/i, '$1')
    .split(/[,;]/)
    .reduce(function(p, a1Notation){
      try{
        var range = as.getRange(a1Notation);
        p.push(Utilities.formatString(
          'Is "%s" a Range? %s', 
          a1Notation,
          !!range.getDisplayValues
        ));
      } catch(err){
        p.push([a1Notation + ' ' + err.message]);
      }
    return p;
  },[]);
}

Pobiera bieżącą formułę i sprawdza, czy jest to Zasięg.

=GFR(A1:A5;1;C1:C2;D1&D2) zwroty

|Is "A1:A5" a Range? true|
|1 Range not found       |
|Is "C1:C2" a Range? true|
|D1&D2 Range not found   |

Dla pełnego składu TC może nazwać coś takiego

var range = as.getRange(a1Notation);
var column = range.getColumn();

Utwórz funkcję i przekaż zakres jako argument:

function fn(input) {
    var cell = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange(SpreadsheetApp.getActiveRange().getFormula().match(/=\w+\((.*)\)/i)[1].split('!'));
    // above ... cell is "range", can convert to array?
    var sum=0;
    for (var i in cell.getValues() ){
        sum = sum + Number(cell.getValues()[i]);
    }  
    return sum;
}

Wykorzystanie w arkuszu kalkulacyjnym:

=fn(A1:A10)

Wyświetli wartość jako sum(A1:A10).