Połączone arkusze

Połączone arkusze umożliwiają analizowanie petabajtów danych bezpośrednio w Arkuszach Google. Możesz połączyć swoje arkusze kalkulacyjne z hurtownią danych BigQuery, by przeprowadzać analizy za pomocą dobrze znanych narzędzi Arkuszy, takich jak tabele przestawne, wykresy i formuły.

Ten przewodnik korzysta z publicznego zbioru danych Shakespeare, aby pokazać, jak używać połączonych arkuszy. Zbiór danych zawiera te informacje:

Pole Typ Opis
słowo STRING Pojedyncze unikalne słowo (gdzie separatorem jest odstęp) wyodrębnione z korpusu.
word_count INTEGER Liczba wystąpień tego słowa w tym korpusie.
korpus STRING Dzieło, z którego wyodrębniono to słowo.
corpus_date INTEGER Rok publikacji tego korpusu.

Jeśli aplikacja żąda danych z połączonych arkuszy, oprócz innych zakresów wymaganych w przypadku zwykłego żądania do interfejsu API Arkuszy Google musi udostępnić token OAuth 2.0, który przyznaje zakres bigquery.readonly. Więcej informacji znajdziesz w artykule Wybieranie zakresów interfejsu API Arkuszy Google.

Zarządzanie źródłem danych

Źródło danych określa zewnętrzną lokalizację, w której znajdują się dane. Źródło danych zostanie połączone z arkuszem kalkulacyjnym.

Dodawanie źródła danych BigQuery

Aby dodać źródło danych, podaj AddDataSourceRequest za pomocą metody spreadsheets.batchUpdate. Treść żądania powinna określać pole dataSource typu DataSource.

"addDataSource":{
   "dataSource":{
      "spec":{
         "bigQuery":{
            "projectId":"PROJECT_ID",
            "tableSpec":{
               "tableProjectId":"bigquery-public-data",
               "datasetId":"samples",
               "tableId":"shakespeare"
            }
         }
      }
   }
}

Zastąp PROJECT_ID prawidłowym identyfikatorem projektu Google Cloud.

Po utworzeniu źródła danych tworzony jest powiązany arkusz DATA_SOURCE, który zapewnia podgląd maksymalnie 500 wierszy. Podgląd nie jest dostępny od razu. Wykonanie jest aktywowane asynchronicznie w celu zaimportowania danych BigQuery.

AddDataSourceResponse zawiera te pola:

  • dataSource: utworzony obiekt DataSource. dataSourceId to unikalny identyfikator ograniczony do arkusza kalkulacyjnego. Jest zapełniana i odwołuje się do niej w celu utworzenia każdego obiektu DataSource ze źródła danych.

  • dataExecutionStatus: stan wykonania, które importuje dane BigQuery do arkusza podglądu. Więcej informacji znajdziesz w sekcji Stan wykonania danych.

Aktualizowanie lub usuwanie źródła danych

Użyj metody spreadsheets.batchUpdate i podaj odpowiednie żądanie UpdateDataSourceRequest lub DeleteDataSourceRequest.

Zarządzanie obiektami źródła danych

Po dodaniu źródła danych do arkusza kalkulacyjnego można na jego podstawie utworzyć obiekt tego źródła danych. Obiekt źródła danych to zwykłe narzędzie Arkuszy, takie jak tabele przestawne, wykresy i formuły, które są zintegrowane z połączonymi arkuszami, aby usprawnić analizę danych.

Istnieją 4 typy obiektów:

  • DataSource tabela
  • DataSource pivotTable
  • DataSource – wykres
  • Formuła DataSource

Dodawanie tabeli źródła danych

Obiekt tabeli, nazywany w edytorze Arkuszy „wyodrębnianiem”, importuje statyczny zrzut danych ze źródła danych do Arkuszy. Podobnie jak w przypadku tabeli przestawnej, tabela jest określona i zakotwiczona do lewej górnej komórki.

Poniższy przykładowy kod pokazuje, jak użyć metody spreadsheets.batchUpdate i UpdateCellsRequest do utworzenia tabeli źródła danych zawierającej maksymalnie 1000 wierszy po 2 kolumnach (word i word_count).

"updateCells":{
   "rows":{
      "values":[
         {
            "dataSourceTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "columns":[
                  {
                     "name":"word"
                  },
                  {
                     "name":"word_count"
                  }
               ],
               "rowLimit":{
                  "value":1000
               },
               "columnSelectionType":"SELECTED"
            }
         }
      ]
   },
   "fields":"dataSourceTable"
}

Zastąp DATA_SOURCE_ID unikalnym identyfikatorem ograniczonym do arkusza kalkulacyjnego, który identyfikuje źródło danych.

Po utworzeniu tabeli źródła danych dane nie są od razu dostępne. W edytorze Arkuszy wyświetla się on jako podgląd. Aby pobrać dane BigQuery, musisz odświeżyć tabelę źródeł danych. RefreshDataSourceRequest możesz określić w obrębie tego samego tagu batchUpdate. Pamiętaj, że wszystkie obiekty źródła danych działają podobnie. Więcej informacji znajdziesz w sekcji Odświeżanie obiektu źródła danych.

Po zakończeniu odświeżania i pobraniu danych BigQuery tabela źródła danych zostanie uzupełniona w ten sposób:

Tabela źródła danych zawierająca dane z publicznego zbioru danych Szekspira.

Dodawanie tabeli przestawnej źródła danych

W przeciwieństwie do konwencjonalnej tabeli przestawnej tabela przestawna źródła danych korzysta ze źródła danych i odwołuje się do danych według nazwy kolumny. Poniższy przykładowy kod pokazuje, jak użyć metody spreadsheets.batchUpdate i UpdateCellsRequest do utworzenia tabeli przestawnej pokazującej łączną liczbę słów w korpusie.

"updateCells":{
   "rows":{
      "values":[
         {
            "pivotTable":{
               "dataSourceId":"DATA_SOURCE_ID",
               "rows":{
                  "dataSourceColumnReference":{
                     "name":"corpus"
                  },
                  "sortOrder":"ASCENDING"
               },
               "values":{
                  "summarizeFunction":"SUM",
                  "dataSourceColumnReference":{
                     "name":"word_count"
                  }
               }
            }
         }
      ]
   },
   "fields":"pivotTable"
    }

Zastąp DATA_SOURCE_ID unikalnym identyfikatorem ograniczonym do arkusza kalkulacyjnego, który identyfikuje źródło danych.

Po pobraniu danych BigQuery tabela przestawna źródła danych jest wypełniana w następujący sposób:

Tabela przestawna źródła danych przedstawiająca dane z publicznego zbioru danych Szekspira.

Dodawanie wykresu źródła danych

Poniższy przykładowy kod pokazuje, jak za pomocą metody spreadsheets.batchUpdate i AddChartRequest utworzyć wykres źródła danych z kolumną chartType o wartości COLUMN, który pokazuje łączną liczbę słów według korpusu.

"addChart":{
   "chart":{
      "spec":{
         "title":"Corpus by word count",
         "basicChart":{
            "chartType":"COLUMN",
            "domains":[
               {
                  "domain":{
                     "columnReference":{
                        "name":"corpus"
                     }
                  }
               }
            ],
            "series":[
               {
                  "series":{
                     "columnReference":{
                        "name":"word_count"
                     },
                     "aggregateType":"SUM"
                  }
               }
            ]
         }
      },
      "dataSourceChartProperties":{
         "dataSourceId":"DATA_SOURCE_ID"
      }
   }
}

Zastąp DATA_SOURCE_ID unikalnym identyfikatorem ograniczonym do arkusza kalkulacyjnego, który identyfikuje źródło danych.

Po pobraniu danych BigQuery wykres źródła danych jest renderowany w taki sposób:

Wykres źródła danych przedstawiający dane z publicznego zbioru danych Szekspira.

Dodawanie formuły źródła danych

Poniższy przykładowy kod pokazuje, jak za pomocą metody spreadsheets.batchUpdate i UpdateCellsRequest utworzyć formułę źródła danych w celu obliczenia średniej liczby słów.

"updateCells":{
   "rows":[
      {
         "values":[
            {
               "userEnteredValue":{
                  "formulaValue":"=AVERAGE(shakespeare!word_count)"
               }
            }
         ]
      }
   ],
   "fields":"userEnteredValue"
}

Po pobraniu danych BigQuery formuła źródła danych jest wypełniana w ten sposób:

Formuła źródła danych pokazująca dane z publicznego zbioru danych Szekspira.

Odświeżanie obiektu źródła danych

Możesz odświeżyć obiekt źródła danych, aby pobrać najnowsze dane z BigQuery na podstawie aktualnych specyfikacji źródła danych i konfiguracji obiektów. Do wywołania RefreshDataSourceRequest możesz użyć metody spreadsheets.batchUpdate. Następnie za pomocą obiektu DataSourceObjectReferences określ co najmniej 1 odwołanie do obiektu, które chcesz odświeżyć.

Pamiętaj, że w ramach jednego żądania batchUpdate możesz zarówno tworzyć, jak i odświeżać obiekty źródła danych.

Stan wykonania danych

Gdy tworzysz źródła danych lub odświeżasz obiekty źródła danych, tworzone jest uruchomienie w tle, które pobiera dane z BigQuery i zwraca odpowiedź zawierającą DataExecutionStatus. Jeśli wykonanie się uruchomi, DataExecutionState zwykle ma stan RUNNING.

Ponieważ proces jest asynchroniczny, aplikacja powinna zaimplementować model odpytywania, aby okresowo pobierać stan obiektów źródła danych. Używaj metody spreadsheets.get, dopóki stan nie zwróci stanu SUCCEEDED lub FAILED. W większości przypadków wykonanie kodu przebiega szybko, ale zależy to od złożoności źródła danych. Zazwyczaj wykonanie nie trwa dłużej niż 10 minut.