Подключенные листы

Connected Sheets позволяет анализировать петабайты данных непосредственно в Google Sheets. Вы можете подключить свои электронные таблицы к хранилищу данных BigQuery или Looker и проводить анализ, используя привычные инструменты Sheets, такие как сводные таблицы, диаграммы и формулы.

Управление источником данных BigQuery

В этом разделе используется общедоступный набор данных BigQuery Shakespeare для демонстрации работы с подключенными таблицами. Набор данных содержит следующую информацию:

Поле Тип Описание
слово STRING Одно уникальное слово (разделителем которого является пробел), извлеченное из корпуса.
количество слов INTEGER Количество раз, которое это слово встречается в данном корпусе.
корпус STRING Произведение, из которого было взято это слово.
corpus_date INTEGER Год публикации данного корпуса.

Если ваше приложение запрашивает данные из BigQuery Connected Sheets, оно должно предоставить токен OAuth 2.0, предоставляющий область действия bigquery.readonly , в дополнение к другим областям действия, необходимым для обычного запроса к API Google Sheets. Для получения дополнительной информации см. раздел «Выбор областей действия API Google Sheets» .

Источник данных указывает внешнее местоположение, где находятся данные. Затем источник данных подключается к электронной таблице.

Добавьте источник данных BigQuery.

Для добавления источника данных укажите объект AddDataSourceRequest , используя метод spreadsheets.batchUpdate . В теле запроса должно быть указано поле dataSource типа DataSource .

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

Замените PROJECT_ID на действительный идентификатор проекта Google Cloud.

После создания источника данных создается связанный лист DATA_SOURCE , позволяющий просмотреть до 500 строк. Предварительный просмотр доступен не сразу. Затем асинхронно запускается выполнение для импорта данных BigQuery.

Объект AddDataSourceResponse содержит следующие поля:

  • dataSource : Созданный объект DataSource . dataSourceId — это уникальный идентификатор, привязанный к конкретной электронной таблице. Он заполняется и используется для создания каждого объекта DataSource на основе источника данных.

  • dataExecutionStatus : Статус выполнения, импортирующего данные BigQuery в лист предварительного просмотра. Дополнительную информацию см. в разделе « Статус выполнения данных» .

Обновить или удалить источник данных BigQuery

Используйте метод spreadsheets.batchUpdate и укажите соответствующий запрос UpdateDataSourceRequest или DeleteDataSourceRequest .

Управление объектами источника данных BigQuery

После добавления источника данных в электронную таблицу можно создать на его основе объект источника данных. Объект источника данных — это стандартный инструмент Google Sheets, такой как сводные таблицы, диаграммы и формулы, интегрированный с подключенными таблицами для проведения анализа данных.

Существует четыре типа объектов:

  • Таблица DataSource
  • DataSource сводная таблица
  • Диаграмма DataSource
  • Формула DataSource

Добавьте таблицу-источник данных BigQuery.

В редакторе таблиц этот объект таблицы называется «выгрузкой» и импортирует статический дамп данных из источника данных в таблицы. Подобно сводной таблице, таблица указывается и прикрепляется к ячейке в верхнем левом углу.

Приведенный ниже пример кода демонстрирует, как использовать метод spreadsheets.batchUpdate и UpdateCellsRequest для создания таблицы-источника данных, содержащей до 1000 строк в двух столбцах ( word и word_count ).

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

Замените DATA_SOURCE_ID на уникальный идентификатор, привязанный к конкретной электронной таблице, который определяет источник данных.

После создания таблицы-источника данные становятся доступны не сразу. В редакторе таблиц они отображаются в виде предварительного просмотра. Для получения данных из BigQuery необходимо обновить таблицу-источник. Вы можете указать запрос RefreshDataSourceRequest в рамках того же batchUpdate . Обратите внимание, что все объекты-источники данных работают аналогично. Для получения дополнительной информации см. раздел «Обновление объекта-источника данных» .

После завершения обновления и получения данных из BigQuery таблица-источник данных заполняется следующим образом:

Таблица источников данных, отображающая данные из общедоступного набора данных о Шекспире.

Добавьте сводную таблицу в качестве источника данных BigQuery.

Unlike a conventional pivot table, a data source pivot table is backed by a data source and it references the data by column name. The following code sample shows how to use the spreadsheets.batchUpdate method and an UpdateCellsRequest to create a pivot table showing the total word count by corpus.

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

Замените DATA_SOURCE_ID на уникальный идентификатор, привязанный к конкретной электронной таблице, который определяет источник данных.

После получения данных из BigQuery сводная таблица источника данных заполняется следующим образом:

Сводная таблица источника данных, отображающая данные из общедоступного набора данных о Шекспире.

Добавьте диаграмму с источником данных BigQuery.

Приведенный ниже пример кода демонстрирует, как использовать метод spreadsheets.batchUpdate и AddChartRequest для создания диаграммы источника данных с chartType COLUMN, отображающей общее количество слов по корпусу.

"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"
      }
   }
}

Замените DATA_SOURCE_ID на уникальный идентификатор, привязанный к конкретной электронной таблице, который определяет источник данных.

После получения данных из BigQuery отображается диаграмма источника данных, как показано на рисунке:

Диаграмма источников данных, отображающая данные из общедоступного набора данных о Шекспире.

Добавьте формулу источника данных BigQuery.

Приведенный ниже пример кода демонстрирует, как использовать метод spreadsheets.batchUpdate и объект UpdateCellsRequest для создания формулы источника данных, вычисляющей среднее количество слов.

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

После получения данных из BigQuery формула источника данных заполняется следующим образом:

Формула источника данных, отображающая данные из общедоступного набора данных о Шекспире.

Обновить объект источника данных BigQuery

Вы можете обновить объект источника данных, чтобы получить самые свежие данные из BigQuery на основе текущих спецификаций источника данных и конфигураций объекта. Для этого используйте метод spreadsheets.batchUpdate для вызова RefreshDataSourceRequest . Затем укажите одну или несколько ссылок на объекты для обновления, используя объект DataSourceObjectReferences .

Обратите внимание, что вы можете как создавать, так и обновлять объекты источника данных в рамках одного запроса batchUpdate .

Управление источником данных Looker

В этом руководстве показано, как добавить источник данных Looker, обновить или удалить его, создать на его основе сводную таблицу и обновить ее.

Ваше приложение, запрашивающее данные из Looker Connected Sheets, будет повторно использовать существующую привязку вашей учетной записи Google к Looker.

Добавьте источник данных Looker.

Для добавления источника данных укажите объект AddDataSourceRequest , используя метод spreadsheets.batchUpdate . В теле запроса должно быть указано поле dataSource типа DataSource .

"addDataSource":{
   "dataSource":{
      "spec":{
         "looker":{
            "instance_uri":"INSTANCE_URI",
            "model":"MODEL",
            "explore":"EXPLORE"
         }
      }
   }
}

Замените INSTANCE_URI , MODEL и EXPLORE на действительные URI экземпляра Looker, имя модели и имя исследования соответственно.

После создания источника данных создается связанный лист DATA_SOURCE , который предоставляет предварительный просмотр структуры выбранного раздела Explore, включая представления, измерения, показатели и любые описания полей.

Объект AddDataSourceResponse содержит следующие поля:

  • dataSource : Созданный объект DataSource . dataSourceId — это уникальный идентификатор, привязанный к конкретной электронной таблице. Он заполняется и используется для создания каждого объекта DataSource на основе источника данных.

  • dataExecutionStatus : Статус выполнения, импортирующего данные BigQuery в лист предварительного просмотра. Дополнительную информацию см. в разделе « Статус выполнения данных» .

Обновить или удалить источник данных Looker

Используйте метод spreadsheets.batchUpdate и укажите соответствующий запрос UpdateDataSourceRequest или DeleteDataSourceRequest .

Управление объектами источников данных Looker

После добавления источника данных в электронную таблицу можно создать на его основе объект источника данных. Для источников данных Looker можно создать только объект сводной таблицы DataSource .

Невозможно создавать формулы, выдержки и диаграммы из источников данных DataSource .

Обновить объект источника данных Looker

Вы можете обновить объект источника данных, чтобы получить самые свежие данные из Looker на основе текущих спецификаций источника данных и конфигураций объекта. Для этого используйте метод spreadsheets.batchUpdate для вызова RefreshDataSourceRequest . Затем укажите одну или несколько ссылок на объекты для обновления, используя объект DataSourceObjectReferences .

Обратите внимание, что вы можете как создавать, так и обновлять объекты источника данных в рамках одного запроса batchUpdate .

Статус выполнения данных

При создании источников данных или обновлении объектов источников данных запускается фоновый процесс, который получает данные из BigQuery или Looker и возвращает ответ, содержащий DataExecutionStatus . Если выполнение запускается успешно, DataExecutionState обычно находится в состоянии RUNNING .

Поскольку процесс асинхронный, ваше приложение должно реализовать модель опроса для периодического получения статуса объектов источника данных. Используйте метод spreadsheets.get до тех пор, пока статус не вернет состояние SUCCEEDED или FAILED . В большинстве случаев выполнение завершается быстро, но это зависит от сложности вашего источника данных. Обычно время выполнения не превышает 10 минут.