Jak określić cały arkusz jako zakres w Arkuszach Google?

Najlepsze obejście, jakie do tej pory znalazłem, to:

worksheet_name!$A$1:$YY

ale idealnie chciałbym móc po prostu pisać, np .:

worksheet_name!

Czy ktoś wie: czy istnieje składnia określająca cały arkusz jako zakres?

Stworzyłem mały fragment Google Apps Script (GAS), aby wykonać pracę za Ciebie.

Kod

function sheetRange(targetName,int) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var asName = ss.getActiveSheet().getSheetName();
  var tgSheet = ss.getSheetByName(targetName);
  var output;

  if(targetName == asName) {
    output = "Error: target sheet is active sheet !!";
  } else {
    switch(int) {
      case 1: 
        output = tgSheet.getDataRange().getValues();
        break;
      case 2:
        output = tgSheet.getSheetValues(1, 1, tgSheet.getMaxRows(),  
          tgSheet.getMaxColumns());
        break;
      default:
        output = "Choose int to be 1 or 2 !!";
    }
  }
  return output;
}

W menu arkusza kalkulacyjnego wybierz Narzędzia> Edytor skryptów i dodaj kod. Pamiętaj, aby nacisnąć przycisk błędu:

Stosowanie

=sheetRange("sheetName",int)

Użyj intopcji opisanej w uwagach.

Przykład

Utworzyłem przykładowy plik: Arkusz jako zakres

Uwagi

W takim przypadku istnieją dwa sposoby ustalenia zakresu w arkuszu kalkulacyjnym za pomocą GAS:

1. int=1; Poprzez getDataRangemetody. Spowoduje to pobranie zakresu, w którym ostatnia kolumna zawiera dane. Te same konta dla liczby wierszy. Jest to zazwyczaj najprostsza trasa. Zobacz drugi arkusz w przykładowym pliku.
2. int=2; Poprzez getSheetValuesmetody. Spowoduje to pobranie zakresu „WYSIWYG”. Zobacz trzeci arkusz w przykładowym pliku.
3. Częstotliwości odświeżania tego rodzaju funkcji niestandardowych nie są natychmiastowe, więc prosimy o cierpliwość. Odświeżenie danych może potrwać kilka godzin.

Referencje

• getDataRange
• getSheetValues

Możesz użyć A: Z lub A: AB lub A: XX (gdzie XX jest ostatnią kolumną strony):

Arkusze Google nie mają składni zakresu dla całego zakresu arkuszy.

Poniższa formuła zwróci adres zakresu całego arkusza o nazwie Arkusz1

="Sheet1!"&ADDRESS(1,1,,TRUE)&":"&ADDRESS(ROWS(Sheet1!A:A),COLUMNS(Sheet1!1:1),,TRUE)

Aby użyć go jako odniesienia, umieść go wewnątrz opcji POŚREDNIE. Poniższa formuła zwróci tablicę wszystkich wartości w Arkuszu1.

= ArrayFormula (
  POŚREDNI(
    „Arkusz1!”
    I ADRES (1,1; PRAWDA)
    & „:”
    I ADRES (RZĘDY (Arkusz1! A: A), KOLUMNY (Arkusz1! 1: 1) ,, PRAWDA)
    ,
    PRAWDZIWE
  )
)

Nie znam konkretnego, ale myślę, że możesz użyć niektórych formuł, jeśli nie znasz liczby wierszy / kolumn:

indirect("Sheet!1:"&countif(Sheet!A:A,"<>@")+countif(Sheet!A:A,"=@"))

Tutaj Sheetjest nazwa twojego arkusza i @jest to dowolny ciąg. Jeśli wybrany arkusz ma @jedną komórkę, nie będzie działać. Możesz go zastąpić innym znakiem, jeśli arkusz zawiera taką komórkę.

COUNITFTutaj zlicza liczbę wierszy w kolumnie A: nie zawierająca @, które powinny być wszystkie, jeśli nie ma komórek zawierających je, i INDIRECTtransformuje Sheet!1:###(tam, gdzie ###jest liczbą wierszy) do rzeczywistego zakresu.

Najlepsze odpowiedzi zostały już podane dla tych, którzy działają w ramach skryptu aplikacji, ale jeśli ktoś tam działa w ramach Arkuszy Google, oto podejście, które może pasować do twoich potrzeb:

"worksheet_name!1:" & ROWS(worksheet_name!A:A)

Objaśnienie : * Zasadniczo określasz zakres za pomocą łańcucha, który jest powiązany z liczbą wierszy w nazwie arkusza roboczego !

Niektóre zalety tego podejścia : * Jest „dynamiczny” ... jeśli dodasz wiersze do dołu, zakres odpowiednio się dostosuje * Lekki - możesz spoliczkować to w funkcję importrange / query

Wady : * Nie wypróbowałem go we wszystkich przypadkach, więc może działać tylko w określonych przypadkach użycia * Osobiście wolę, aby rzeczy były dynamiczne / czyste, więc niestandardowa funkcja byłaby dobrym pośrednikiem między skryptem aplikacji a tym lekkim podejściem

To działa dla mnie:

var ss = SpreadsheetApp.getActive();
var sh = ss.getSheets()[0];
var rg = sh.getName() +"!"+ sh.getDataRange().getA1Notation();

W nazwie skryptu połącz nazwę arkusza z „!” a następnie pobierz notację A1 zakresu danych na wybranym arkuszu

Byłem w stanie wybrać cały arkusz, nazywając zakres jako arkusz bez wykrzyknika.

Miałem zakładkę o nazwie dane. Zamiast korzystać z danych! lub dane! A1: ZZ.

Aby określić cały arkusz jako zakres, użyj:

worksheet_name!A1:ZZ

możesz spróbować tutaj: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear

lub jeśli używasz Java (aby wyczyścić wszystko w Arkuszu1):

Sheets service = getSheetsService(credential);
service.spreadsheets().values().clear(REPORT_WARNINGS_LATAM_FILEID, "Sheet1!A1:ZZ", new ClearValuesRequest()).execute();

lub jeśli chcesz po prostu użyć go w formule (na przykład: SUMA) na innym arkuszu (na przykład: Arkusz2), możesz użyć następującego odniesienia:

=SUM(Sheet1!A1:ZZ)

^ to zsumuje wszystkie istniejące komórki na Arkuszu1 i umieści wartość w komórce na Arkuszu 2, możesz znaleźć przykład tutaj: https://docs.google.com/spreadsheets/d/1rP3YCl3ErlYjlYGT_Q-xFvkVr7yKw6WQfHklbohL1NM/edit?usp=sharing . W tym przykładzie mamy 3 kolumny i 5 wierszy na Arkuszu1, więc ta formuła = SUMA (Arkusz1! A1: ZZ) wybiera wszystkie z nich. Możesz dodać wiersze lub kolumny do arkusza 1, a to rozwiązanie nadal wybierze wszystkie komórki.

PS: jeśli głosujesz w dół moją odpowiedzią - wyjaśnij dlaczego.

Zakładając, że znasz liczbę wierszy, możesz nazwać cały arkusz jako „nazwa arkusza roboczego”:

KROKI:

1. Wybierz cały arkusz
2. Kliknij „Dane” -> „Nazwane i chronione zakresy”
3. Wpisz „nazwa arkusza roboczego”, aby nazwać wybór i kliknij Gotowe.

Teraz za każdym razem, gdy użyjesz „nazwa arkusza roboczego” w funkcji, będzie ona odnosić się do całego arkusza roboczego.