google tables api php
Google Sheets API Overview
The Google Sheets API is a RESTful interface that lets you read and modify a spreadsheet’s data. The most common uses of this API include the following tasks:
Below is a list of common terms used in the Sheets API:
The primary object in Google Sheets that can contain multiple sheets, each with structured information contained in cells. Every spreadsheet is represented by a Spreadsheet resource and has a unique spreadsheetId value, containing letters, numbers, hyphens, or underscores. You can find the spreadsheet ID in a Google Sheets URL:
https://docs.google.com/spreadsheets/d/ spreadsheetId /edit#gid=0
A page or tab within a spreadsheet. Each sheet is represented by a Sheet resource and has a unique title and numeric sheetId value. You can find the sheet ID in a Google Sheets URL:
An individual field of text or data within a sheet. Cells are arranged in rows and columns, and can be grouped together as a range of cells. Each cell is represented by a CellData resource, but doesn’t have a unique ID value. Instead, cells are identified by their row and column coordinates.
A syntax used to define a cell or range of cells with a string that contains the sheet name and starting and ending cell coordinates using column letters and row numbers. This method is most common and useful when referencing an absolute range of cells.
Show examples
A syntax used to define a cell or range of cells with a string that contains the sheet name and starting and ending cell coordinates using row numbers and column numbers. This method is less common, but can be useful when referencing a range of cells relative to a given cell’s position.
Show examples
A defined cell or range of cells with a custom name to simplify references throughout an application. A named range is represented by a FilterView resource.
A defined cell or range of cells that cannot be modified. A protected range is represented by a ProtectedRange resource.
Next steps
To get started with the Google Sheets API:
To learn about developing with Google Workspace APIs, including handling authentication and authorization, refer to Get started as a Workspace developer.
To learn how to configure and run a simple Sheets API app, read the Quickstarts overview.
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Что нам стоит автоматизацию построить. Использование HTTP API в Google Sheets
В эпоху повальной автоматизации пользователям хочется «нажать на кнопку и получить ответ». Ну или дополнительно немного подвигать мышкой. Автоматизация же отчетов и других штук, которые удобно представить в виде таблички, часто строится в Excel с использованием своих макросов или же просто встроенных формул. Плагинами к Excel нынче никого уже не удивишь, кстати, у нас такой тоже есть, но это предмет отдельной статьи. А как насчет Google Sheets? Ранее мой коллега рассказывал, как можно прикрутить наше API к Telegram, я же попробую рассказать, как использовать его в гуглотаблицах.
Под катом чуть-чуть кода и много костылей.
Работать мы будем, очевидно, в браузере. Для написания своих функций будем использовать Google Apps Script, который по синтаксису подозрительно похож на урезанный javascript. Исходим из принципа, что кодить мы не умеем, а читать документацию не хотим, зато активно используем подходы, изложенные в технике Stackoverflow Driven Development.
Если вы начинающий трейер, то предлагаем вам почитать тут.
Подготовка
Для начала получаем доступ к API. Бесплатно (если только аккаунт-менеджеры не замучают звонками) и без смс, но с регистрацией. Документацию читать не будем (все равно там картинок нет), а токен для доступа мы сгенерируем руками через jwt.io. Почему руками? Потому что токен, генерируемый нашим сайтом, истекает через час. Это полезно, например, для использования на вебсайте, но для нормальной работы в Sheets мы хотим, чтобы он жил дольше, допустим, год. Подробнее о процедуре создания токена можно почитать здесь.
Работа с API
Теперь создаем пустую таблицу и идем в редактор скриптов; если кто не знает, попасть туда можно путем вызова Tools → Script editor. В редакторе объявим несколько глобальных переменных:
Также зададим функции для работы с запросами:
Подробнее про UrlFetchApp и его аргументы можно почитать здесь. Дополнительно мы вылавливаем коды, отличные от 200, и показываем пользователю «человекочитаемую» ошибку из запроса.
Статическая информация из API
Сейчас попробуем прикрутить несколько вызовов нашего API. Здесь все-таки пришлось открыть документацию и убедиться, что красивых картинок там действительно нет.
Для начала напишем метод, реализующий запрос финансовых инструментов. Как мне подсказывают, для экономии трафика информацию об инструментах разделили в два конца — /symbols/:symbolId и /symbols/:symbolId/specification :
Затем создадим аналогичные методы для работы с опционами и фьючерсами.
Котировки и «свечки»
Тогда запрос наподобие EXANTEOHLC(«EUR/USD.E.FX», 60, «high») вернет нам максимальную цену за последнюю минуту.
С котировками чуть сложнее. На момент написания статьи единственное API для получения котировок — это стрим, который неудобно использовать в Apps Script. (Кстати, обещают добавить новое API для единичной котировки в будущих релизах). Поэтому пришлось накостылить решение из имеющихся средств. По построению, close незакрытой свечки (то есть за текущие минуту/час/день) — это среднее между последними пришедшими ценами покупки и продажи, поэтому:
Для полного счастья можно еще сделать функцию конвертации из одной валюты в другую:
Использование
Кстати, на первый взгляд, можно было бы просто использовать функцию NOW() (она одна из немногих умеет пересчитываться раз в минуту, при наличии специальной галочки в настройках Юзабилити), но ее значение нельзя передать в пользовательскую функцию, печаль.
Для автоматического обновления данных раз в минуту можно создать триггер для написанной функции в Edit → Current project’s triggers:
О, кажется, теперь с этим можно работать. Давайте попробуем взять ближайший фьючерсный контракт на FORTS:Si (который USD/RUB) и посмотреть на его свечки:
Но мы же говорим об автоматизации, почему бы нам не сделать такую табличку для 100 инструментов сразу? Ой.
Но методы обхода этой проблемы я предлагаю найти читателю самостоятельно 🙂 Вероятно, не лучшее, но вполне рабочее решение для однотипных запросов, где мы забираем из JSON только одно поле (например, EXANTEOHLC ) — использовать кэш в глобальных переменных. Более правильное решение — в одном запросе (например, для свечек) посылать списки из нескольких финансовых инструментов, разделенных запятой.
Документация
В таком случае пользователь увидит красивую справочку о том, как правильно использовать данную функцию, какие аргументы она требует и что возвращает. Следует отметить, что парсит гугл докстринг по своему усмотрению, но в целом очень похоже на JSDoc.
На этом все. Кажется, теперь можно пользоваться и опубликовать. Только токен вырежьте 🙂 Исходный код этого «скрипта» можно найти на гитхабе под MIT лицензией.
Как работать с API Google Таблиц (Google Sheets API v4) на языке R с помощью нового пакета googlesheets4
Электронные таблицы по-прежнему остаются довольно популярным инструментом для работы с данными, а среди различных процессоров электронных таблиц наиболее популярными являются Google Таблицы. Во-первых, это бесплатный инструмент, во-вторых, функционал Google Таблиц достаточно широк, и они предоставляют вам возможность в онлайн режиме получить доступ к данным.
Для тех кому лень читать статью, вот ссылка на 10 минутный, русскоязычный видео урок на YouTube. Остальным добро пожаловать под кат.
Содержание
Если вы интересуетесь анализом данных, и в частности языком R, возможно вам будут интересны мои telegram и youtube каналы. Большая часть контента которых посвящена языку R.
Зачем переходить на работу с пакетом googlesheet4
На самом деле по функционалу они похожи, возможно даже googlesheets пока имеет больше возможностей, но он работает только с Google Sheets API V3. Google несколько месяцев назад сообщил, что поддержка 3 версии данного API будет прекращена 3 марта 2020 года.
Миграция с googlesheets на googlesheets4
Что бы максимально упростить миграцию с устаревшего пакета googlesheets на новый googlesheets4 я решил добавить таблицу соответвия новых функций старым.
Установка пакета googlesheets4
Установить его можно как из CRAN, так и из GitHub.
Установка из GitHub:
Авторизация
В пакете googlesheets4 есть по меньшей мере 3 способа авторизации:
Авторизация со стандартными значениями аргументов
Наиболее простой способ авторизации предоставляет функция gs4_auth() со значением аргументов принятых в ней по умолчанию.
Минус этого подхода заключается в том, что вы будете использовать приложение вшитое в пакет по умолчанию, как и 90% других его пользователей. Каждое приложение имеет квоты на количество отправляемых запросов, поэтому с ростом количества пользователей данного пакета возрастает и шанс выйти за выделенные лимиты.
Поэтому я рекомендую самостоятельно создать приложение в Gogle Cloud, и пройти авторизацию с его помощью.
Авторизация через собственное приложение
Как создать своё приложение?
Для создания приложения следуйте приведённым ниже инструкциям:
Скачиваем JSON
Копируем ID и секрет приложения
Если вы впервые создаёте приложение в Google Console, то также вам предварительно понадобиться создать «Окно запроса доступа OAuth», просто заполните в нём название и ваш email, насколько я помню больше там никаких обязательных полей нет.
Последний шаг, включаем Google Sheets API, для этого достаточно перейти по этой ссылке и нажать кнопку включить API.
Теперь возвращаемся в RStudio, подключаем пакет и проходим авторизацию через своё приложение.
Проходим авторизацию через JSON файл:
Проходим авторизацию указав в коде ID и Секрет приложения:
Авторизация через сервисный аккаунт
Для начала надо создать сервисный аккаунт, вот алгоритм действий который вам необходимо выполнить:
Теперь у вас появился сервисный аккаунт, откройте его и скопируйте почту.
При авторизации под сервисным аккаунтам вы можете работать только с теми Google Таблицами к которым вы предоставили доступ для созданного сервисного аккаунта по его почте. Доступ предоставляется также, как и обычному пользователю.
Проходим авторизацию через сервисный аккаунт:
При авторизации через сервисный аккаунт вам не понадобится подтверждать доступ к данным через браузер, но ещё раз напомню, что вам надо отдельно расшаривать доступ сервисному аккаунту к каждой Google Таблице, с которой вам необходимо работать по API.
Разница между авторизацией через приложение и через сервисный аккаунт
Основными отличиями между авторизацией через приложение и через серверный аккаунт являются:
По этим двум причинам наиболее предпочтительным способом автризации является авторизация через сервисный аккаунт. Но важно понимать, что если кто-то посторонний получит доступ к JSON файлу с ключём от сервисного аккаунта, он автоматически завладеет всеми правами и доступами которые вы предоставили этому сервисному аккаунту.
Основные функции пакета googlesheets4
Все функции пакета googlesheets4 разделены на 3 группы, каждая группа функций имеет свой префикс, который говорит об области действия этой функции:
Давайте рассмотрим основные функции пакета googlesheets4.
Пакет содержит также множество вспомогательных функций, но думаю перечисленных выше вам будет более чем достаточно для выполнения любых действий с Google Таблицами.
Пример работы с API Google Таблиц на языке R
Теперь я приведу примеры кода для выполнения основных операций с Google Таблицами.
Загрузка данных из существующих Google Таблиц
Считывание данных из Google Таблицы является наиболее часто используемой операцией.
Алгоритм действий достаточно прост, вам необходимо инициализировать подключение к нужной таблице, и считать данные с существующего в ней листа.
Пример кода для чтения данных из Google Таблицы
Процесс подключения и чтения данных можно записать более компактно используя пайплайны.
Создание Google Таблиц с помощью API
Для создания новой таблицы используйте функцию gs4_create() и следующие её аргументы.
Пример создания Google Таблицы
Приведённый выше код создаст новую Google Таблицу «my_new_dox», в которой будут 2 листа: mtcars, iris.
Создание нового листа в Google Таблице
Теперь мы можем проводить с созданной таблицей различные манипуляции, например создать в ней новый лист.
Пример кода для создания нового листа с данными
Функция sheet_write() имеет 3 основных аргумента:
Дописывание строк в существующий лист
Ещё одна достаточно важная операция — добавление данных на уже существующий лист.
При создании таблицы my_new_dox мы записали на лист iris только первые 6 строк с данными, давайте допишем оставшиеся.
Пример кода для добавления строк на существующий лист
Перемещение Google Таблиц между папаками Google Диска
Установка googledrive
Пример кода для перемещения Google Таблицы из одной папки Google Диска в другую
Полезные ссылки
В этом разделе приведу несколько полезных ссылок по теме статьи:
Заключение
Описанных в статье возможностей пакета googlesheets4 достаточно для решения подавляющего большинства задач, в которых необходимо использовать Google Sheets API.
На данный момент googlesheets4 находится в стадии активной разработки. Автор пакета планирует реализовать его функционал в полном объёме к марту 2020 года, в связи с чем в статье возможны корректировки и дополнения по мере изменения или расширения возможностей пакета.
Если вы дочитали до этого параграфа, то наверняка интересуетесь, и скорее всего уже используете язык R в работе. Если это так, то думаю вам будет интересен мой телеграм и youtube каналы, большая часть контента которых посвящена языку R.
Добавление и создание таблиц в Google Docs через PHP
В данной статье я попытаюсь вам объяснить как быстро сделать подключение ваших PHP скриптов к Гугл докам, и создать таблицу в Google Sheets (Sheetspread), обновить ячейки, удалить или создать лист в таблице.
«Нагугливая» много информации по тому как создать таблицы в Google Docs я не нашел ничего полезного, но всё же есть немного полезных ссылочек. И вот я вам скину самые полезные из них:
А теперь я попытаюсь вам лично объяснить как использовать таблицы Google через PHP в кратце.
Для начала вам понадобиться Composer и несколько зависимостей.
Мой Composer.json выглядит вот так:
Исходя из него вы сами сможете составить свой Composer.json, самое важное это composer require asimlqt/php-google-spreadsheet-client и google/apiclient
После подключения всех зависимостей, пробуем создать таблицу по уже произведенной авторизации, у вас должен быть accessToken который получают при авторизации, с правами: ’email’, ‘https://spreadsheets.google.com/feeds’, ‘https://www.googleapis.com/auth/drive.file’
Чтобы просто создать файл в Google Drive производим следующие действия:
Вуаля, наш файл создан.
Далее чтобы получить наш spreadsheet производим следующие действия.
В итоге мы получили нашу таблицу которая готова к изменениям.
Попробуем удалить наш первый лист который по-умолчанию размеров 100х10, где 100 строк, и 10 колонок, это обычно мало.
Мы удалили первый наш лист в таблице. Но на замену нужно создать другой лист других размеров, соответственно наш код измениться так:
Теперь в нашей таблице только 1 лист наших размеров.
Для заполнения таблиц есть очень великолепный инструмент как BatchRequest, посылаем запросы «стопкой», а не по одному:
В результате этого у нас будет 1 столбец с записями Ячейка заполнена ровно в 8 строк.
На этом всё, урок закончен. Теперь вы можете создавать таблицы, создавать листы, удалять листы, и заполнять данными листы в таблицах.
Внимание! Действует ограничение на количество ячеек на всей таблице это 500.000 штук.
также после смены WordsheetFeed (getByTitle) нужно обязательно производить Wordsheet (getCellFeed)
Reading & Writing Cell Values
Spreadsheets can have multiple sheets, with each sheet having any number of rows or columns. A cell is a location at the intersection of a particular row and column, and may contain a data value. The Google Sheets API provides the spreadsheets.values collection to enable the simple reading and writing of values.
This page describes the basics of using the spreadsheets.values collection. If you need to update formatting or other properties in a sheet, you will need to use the spreadsheets collection, which is described in Updating Spreadsheets.
Methods
The spreadsheets.values collection provides the following methods for reading and writing values, each with a specific task in mind:
| Range Access | Reading | Writing |
|---|---|---|
| Single range | spreadsheets.values.get | spreadsheets.values.update |
| Multiple ranges | spreadsheets.values.batchGet | spreadsheets.values.batchUpdate |
| Appending | spreadsheets.values.append |
In general, it is a good idea to combine multiple reads or updates with the batchGet and batchUpdate methods (respectively), as this will improve efficiency.
You can find examples of each of these methods in the Basic Reading and Basic Writing samples pages.
Reading
To read data from a sheet, you will need the spreadsheet ID and the range(s) A1 notation. For more information about spreadsheet IDs and A1 notation, see Key Concepts in the Google Sheets API. The format of the output is controlled by three optional parameters:
| Format Parameter | Default Value |
|---|---|
| majorDimension | ROWS |
| valueRenderOption | FORMATTED_VALUE |
| dateTimeRenderOption | SERIAL_NUMBER |
The singular and batch get methods are described below. For examples of basic read operations, see the Basic Reading recipes page.
Reading a single range
To read a single range of data out of a spreadsheet, use a spreadsheets.values.get request:
Apps Script
JavaScript
Node.js
Python
The response to this request is returned as a ValueRange object.
Reading multiple ranges
To read multiple discontinuous ranges, use a spreadsheets.values.batchGet, which lets you specify any number of ranges to retrieve:
Apps Script
JavaScript
Node.js
Python
The response to this request is a BatchGetValueResponse, object which contains the spreadsheet ID and a list of ValueRange objects.
Writing
To write to a sheet, you will need the spreadsheet ID, the range(s) in A1 notation, and the data you wish to write arranged in an appropriate request body object. For more information about spreadsheet IDs and A1 notation, see Key Concepts in the Google Sheets API.
Updates require a valid ValueInputOption parameter (for singular updates, this is a required query parameter; for batch updates, this parameter is required in the request body). The ValueInputOption controls whether input strings are parsed or not, as described in the following table:
The singular and batch update methods are described below. For examples of basic write operations, see the Basic Writing recipes page.
Writing to a single range
To write data to a single range, use a spreadsheets.values.update request:
Apps Script
JavaScript
Node.js
Python
When updating, values with no data are skipped. To clear data, use an empty string («»).
Writing multiple ranges
If you want to write multiple discontinuous ranges, you can use a spreadsheets.values.batchUpdate request:
Apps Script
JavaScript
Node.js
Python
Appending values
To append data after a table of data in a sheet, use a spreadsheets.values.append request:
Apps Script
JavaScript
Node.js
Python
The input range is used to search for existing data and find a «table» within that range. Values are appended to the next row of the table, starting with the first column of the table. For example, consider a sheet Sheet1 that looks like:
| A | B | C | D | E |
| 1 | x | y | z | |
| 2 | x | y | z | |
| 3 | ||||
| 4 | x | y | ||
| 5 | y | z | ||
| 6 | x | y | z | |
| 7 |
The following range inputs would not start writing at B7 :
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.