Последние полгода я создаю достаточно много файлов и скриптов в Google Sheet, решил поделиться знаниями, потому что знаю, что бывает сложно пробить барьер входа без хорошего туториала (в наше время особенно ценно то, что можно этот туториал найти в виде статьи, а не видео, ИМХО)
В статье будет рассмотрены следующие темы
- создание скрипта в google sheet (Apps Script)
- запрос данных по api (etherscan)
- вывод результата на лист
- создание кнопки для запуска скрипта
Дисклеймер о качестве кода
Я никогда не работал программистом, никогда не пытался им стать хотя и написал некоторое количество кода на нескольких языках программирования. Стилистически этот код далек от идеала и не соблюдается множество паттернов принятых в профессиональной среде. Но он работает и выполняет свою функцию.
Немного про крипту
Большая часть данных в крипте находится в открытом доступе — любой человек может зайти посмотреть баланс практически любого кошелька или историю транзакций. Самый простой способ — воспользоваться так называемыми эксплорерами. Самая популярный блокчейн (как по количеству монет, которые на нем выпущены, так и по объемам средств) — это ERC20, который базируется на эфире (ETH). Соответственно, самый популярный эксплорер — etherscan.io
Получаем API-ключ
У большинства сервисов, которые позволяют запрашивать данные программным образом — есть API, что это такое и зачем оно нужно можно почитать тут. Не исключение и etherscan.
Чтобы зарегистрироваться и получить ключ — переходим по ссылке и заполняем появившиеся поля — имя пользователя, email, еще раз свой email, пароль, еще раз пароль, пару согласий и капчу.

На почту придет письмо со ссылкой, по которой нужно перейти для подтверждения аккаунта

После этого переходим по ссылке авторизации, повторно вводим логин / пароль и переходим в закрытую часть сайта. В левой части находим секцию API Keys — нам сюда.

Далее в центральной части в секции My API Keys нажимаем на кнопку Add, в появившемся окошке выдаем имя ключу (например, GoogleSheet), нажимаем на Create New API Key и получаем заветный ключ

Копируем его к себе в блокнот — он нам понадобится в скрипте

Изучаем API документацию
Если сделать поисковый запрос {service_name} api docs, то в большинстве случаев на первой строчке будет выпадать ссылка на раздел с документацией по сервису для разработчиков. Там можно ознакомится с эндпоинтами, параметрами запросов и форматом ответа (если не очень понятно о чем речь — можно еще раз ознакомиться о том что такое api по ссылке тут, ну или далее будет понятнее по ходу создания скрипта)
Для etherscan эта ссылка выглядит так. Какой именно эндпоинт нужно использовать в том или ином случае — зависит от конкретной задачи и от возможностей того или иного эндпоинта.

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

Протестировать эндпоинт для etherscan (как и для большинства эксплореров которые я видел) — можно просто из браузера. Главное собрать урл по примеру, который выдается в документации.
В нашем случае — мы соберем транзакции для адреса 0x31be3f5a0f2c0acacde1440e1196e78c37b938a3. Упрощенная ссылка (без опциональных параметров) будет выглядеть таким образом:
Если открыть ее в браузере — результатом будет много буковок, которые при ближайшем рассмотрении окажутся JSON файлом. Как его обрабатывать — расскажу позднее, сейчас самое главное — мы получили результат, с которым дальше будем работать и обрабатывать в Google Sheets

Создаем скрипт. Apps Scripts
Ну, с подготовительной часть закончили — теперь перейдем в Google Sheets. Создаем новый файл Google Sheets — я обычно делаю это через drive.google.com

Давайте сразу дадим файлу название — укажем его в левом верхнем углу и нажмем на enter

И переименуем лист (он там один по умолчанию) — назовем его tx

Далее откроем редактор скриптов. Extension > Apps Script

и также дадим ему имя по названию файла — etherscan_parser (название справа от логотипа Apps Script). И сотрем предварительно созданные строчки кода в центральной части


Все, можно начинать писать код.
В самом начале объявим переменные, к которым дальше будем обращаться. Первая переменная ss — отвечает за активную книгу Google Sheets, вторая переменная — за лист tx
const ss = SpreadsheetApp.getActiveSpreadsheet();
const s_tx = ss.getSheetByName('tx');
Далее — обявим переменные, которые относятся к API
const api_key = '49R5KT8FPEKFPV5DM1JMBTS978H72VEEB1';
const base_url = 'https://api.etherscan.io/api';
const addr = '0x31be3f5a0f2c0acacde1440e1196e78c37b938a3';
api_key — содержит ключ, который мы получили ранее на сайте etherscan
base_url — ссылка на api, который мы будем использовать
addr — адрес, для которого мы будем собирать транзакции.
Мы выносим эти переменные в самое начало для того, чтобы можно было их быстро заменить при необходимости
Теперь необходимо создать функцию, которая будет генерировать url по предоставленным параметрам. Я считаю хорошим правилом перед такой функцией указывать ссылку на документацию, полный пример по документации и пример реального url, который будет создаваться. Звучит (да и выглядит) громоздко, но поверьте — это может сэкономить время на отладке в будущем
/*
https://docs.etherscan.io/api-endpoints/accounts#get-a-list-of-erc20-token-transfer-events-by-address
https://api.etherscan.io/api?module=account&action=tokentx&address=0x31be3f5a0f2c0acacde1440e1196e78c37b938a3&apikey=49R5KT8FPEKFPV5DM1JMBTS978H72VEEB1
https://api.etherscan.io/api
?module=account
&action=tokentx
&contractaddress=0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2
&address=0x4e83362442b8d1bec281594cea3050c8eb01311c
&page=1
&offset=100
&startblock=0
&endblock=27025780
&sort=asc
&apikey=YourApiKeyToken
*/
function url_etherscan_tx(adrs) {
var u = base_url
u += '?module=account'
u += '&action=tokentx'
u += '&address=' + adrs
u += '&apikey=' + api_key
return u
}
Эта функция принимает в качестве аргумента только один аргумент — адрес, все остальные аргументы либо передаются как ранее объявленные переменные (api), либо по умолчанию — module / action.
Давайте сразу напишем функцию, которая протестирует результат — правильно ли он формирует ссылку или нет.
function test_url() {
Logger.log(url_etherscan_tx(addr))
}
Функция выведет в специальном окне результат выполнения функции url_etherscan_tx по указанному ранее в переменной адресе.
Чтобы выполнить функцию — для начала сохраните проект — нажав на иконку дискеты под названием проекта

Теперь выберете из выпадающего списка функцию test_url и нажмите на кнопочку Run

При первом запуске появится окно с запросом разрешения на запуск — ответьте Review permissions > Выберете свой аккаунт > (если тут появится окошко со страшным значком, то нажмите advanced > go to etherscan) > allow


И в нижней части экрана появится окошко execution log, в котором появится ТА САМАЯ ссылка, которая нам нужна

Проверяем себя — откроем эту ссылку в браузере и должны появится данные как и при тесте ранее. Если все ОК — переходим дальше.
Ссылку мы создали, дальше необходимо забрать данные по этой ссылке. Для этого создадим еще одну маленькую и полезную функцию
function get_result_from_url(url='') {
const res = UrlFetchApp.fetch(url)
return res.getContentText()
}
Она принимает в качестве аргумента url и забирает результат по этому url
… И добавим еще одну функцию AS IS, которую я очень часто использую (там есть ссылка на первоисточник). Она нам потребуется чтобы «разворачивать» массивы для вставки на лист Google Sheets
// Morphs a 1-d array into a 2-d array for use with Range.setValues([][])
// https://stackoverflow.com/questions/40552779/how-to-parse-json-response-in-google-app-script
function morphIntoMatrix(array) {
// Create a new array and set the first row of that array to be the original array
// This is a sloppy workaround to "morphing" a 1-d array into a 2-d array
var matrix = new Array();
matrix[0] = array;
// "Sanitize" the array by erasing null/"null" values with an empty string ""
for (var i = 0; i < matrix.length; i ++) {
for (var j = 0; j < matrix[i].length; j ++) {
if (matrix[i][j] == null || matrix[i][j] == "null") {
matrix[i][j] = "";
}
}
}
return matrix;
}
Теперь можно приступить к написанию функции, которая будет обрабатывать результат, который мы забрали по ссылке. И первым делом в этой фунции создадим url и заберем результат в переменную, используя функции, которые создали ранее
function set_tx() {
var url = url_etherscan_tx(addr)
var resp = get_result_from_url(url)
}
Результат, который мы получаем по этой ссылке — это JSON файл, поэтому мы можем использовать специальную фунцию, чтобы обработать такую структуру. Запишем результат в переменную res
var res = JSON.parse(resp)
Структура у получаемого ответа — status, message, result. В message мы получаем ответ от etherscan, если там все ОК, то можем идти дальше и смотреть что находится в переменной result. Причины, почему там может быть не ОК — например, мы запросили результат по транзакциям для периода, который не наступил (помните о необязательных параметрах startblock и endblock ?)

Для нас это означает, что лучше проверять значение в message, прежде чем идти дальше, поэтому напишем условие, и чтобы далеко не забегать — проверим как отрабатывает функция. Ниже она представлена полностью на текущем этапе
function set_tx() {
var url = url_etherscan_tx(addr)
var resp = get_result_from_url(url)
var res = JSON.parse(resp)
if (res['message'] == 'OK') {
Logger.log('parse - OK')
}
}
Сохраняем проект, запускаем функцию. Если все ОК, то в нижней части экрана появится строка parse — OK

Далее — внутри блока проверки результата добавим еще несколько переменных — они нам потребуются в следующей части
var res_rows = res['result']
var r = res_rows.length
var c = Object.keys(res_rows[0]).length
res_rows — будет хранить строчки с транзакциями
r — количество строк
c — количество столбцов в этих транзакциях
Добавим блок, который будет заполнять шапку
// fill header
var arr_hdr = new Array(c)
for (var j = 0; j <= c -1; j++ ) {
arr_hdr[j] = Object.keys(res_rows[0])[j]
}
s_tx.getRange(1,1,1,c).setValues(morphIntoMatrix(arr_hdr))
и блок, который будет выгружать сами транзакции
// fill tx
var hdr = Object.keys(res_rows[0])
var arr = new Array(r)
for (var i = 0; i <= r -1; i++ ) {
arr[i] = new Array(c)
for (var j = 0; j <= c -1; j++ ) {
arr[i][j] = res_rows[i][hdr[j]]
}
}
s_tx.getRange(2, 1, r, c).setValues(arr)
соединяем все вместе, сохраняем проект и пробуем запустить скрипт. На этот раз внизу в окошке ничего не появится, но если открыть Google Sheets — там должны появиться данные
function set_tx() {
var url = url_etherscan_tx(addr)
var resp = get_result_from_url(url)
var res = JSON.parse(resp)
if (res['message'] == 'OK') {
var res_rows = res['result']
var r = res_rows.length
var c = Object.keys(res_rows[0]).length
// fill header
var arr_hdr = new Array(c)
for (var j = 0; j <= c -1; j++ ) {
arr_hdr[j] = Object.keys(res_rows[0])[j]
}
s_tx.getRange(1,1,1,c).setValues(morphIntoMatrix(arr_hdr))
// fill tx
var hdr = Object.keys(res_rows[0])
var arr = new Array(r)
for (var i = 0; i <= r -1; i++ ) {
arr[i] = new Array(c)
for (var j = 0; j <= c -1; j++ ) {
arr[i][j] = res_rows[i][hdr[j]]
}
}
s_tx.getRange(2, 1, r, c).setValues(arr)
}
}
Теперь последний штрих — добавим функцию, которая будет осуществлять вызов set_tx(), но перед этим будет очищать лист (мы же должны увидеть, что значения действительно подгружаются заново, верно ?)
function run_fetcher() {
s_tx.getRange(1,1,9000,20).clearContent()
set_tx()
}
Добавляем кнопку для запуска скрипта
Есть несколько способов как запустить скрипт (как и в VBA) — через эвенты, напрямую из редактора кода и, пожалуй, один из самых удобных способов — по кнопке.
Чтобы добавить кнопку переходим Insert > Drawing

Выбираем фигуры и какую-нибудь прямоугольную фигуру. Отрисовываем прямоугольних на «холсте». Два раза щелкаем на фигуре и назначаем текст. Например «Refresh». Если место на фигуре еще осталось после написания текста — лучше уменьшить размер фигуры. Нажимаем Save & Close, тем самым мы вставим кнопку на лист.


Теперь выделите фигуру — в правом верхнем углу должны появиться три точки. Нажмите на них и в выпадающем списке выберите Assign a script.

В появившемся окне укажите название скрипта (run_fetcher) и нажмите на ОК

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

Когда скрипт закончит работу — вы увидите, что плашка сменилась и теперь там Finished script

Полная версия скрипта
const ss = SpreadsheetApp.getActiveSpreadsheet();
const s_tx = ss.getSheetByName('tx');
const api_key = '49R5KT8FPEKFPV5DM1JMBTS978H72VEEB1';
const base_url = 'https://api.etherscan.io/api';
const addr = '0x31be3f5a0f2c0acacde1440e1196e78c37b938a3';
function test_url() {
Logger.log(url_etherscan_tx(addr))
}
/*
https://docs.etherscan.io/api-endpoints/accounts#get-a-list-of-erc20-token-transfer-events-by-address
https://api.etherscan.io/api?module=account&action=tokentx&address=0x31be3f5a0f2c0acacde1440e1196e78c37b938a3&apikey=49R5KT8FPEKFPV5DM1JMBTS978H72VEEB1
https://api.etherscan.io/api
?module=account
&action=tokentx
&contractaddress=0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2
&address=0x4e83362442b8d1bec281594cea3050c8eb01311c
&page=1
&offset=100
&startblock=0
&endblock=27025780
&sort=asc
&apikey=YourApiKeyToken
*/
function url_etherscan_tx(adrs) {
var u = base_url
u += '?module=account'
u += '&action=tokentx'
u += '&address=' + adrs
u += '&apikey=' + api_key
return u
}
function get_result_from_url(url='') {
const res = UrlFetchApp.fetch(url)
return res.getContentText()
}
function run_fetcher() {
s_tx.getRange(1,1,9000,20).clearContent()
set_tx()
}
function set_tx() {
var url = url_etherscan_tx(addr)
var resp = get_result_from_url(url)
var res = JSON.parse(resp)
if (res['message'] == 'OK') {
var res_rows = res['result']
var r = res_rows.length
var c = Object.keys(res_rows[0]).length
// fill header
var arr_hdr = new Array(c)
for (var j = 0; j <= c -1; j++ ) {
arr_hdr[j] = Object.keys(res_rows[0])[j]
}
s_tx.getRange(1,1,1,c).setValues(morphIntoMatrix(arr_hdr))
// fill tx
var hdr = Object.keys(res_rows[0])
var arr = new Array(r)
for (var i = 0; i <= r -1; i++ ) {
arr[i] = new Array(c)
for (var j = 0; j <= c -1; j++ ) {
arr[i][j] = res_rows[i][hdr[j]]
}
}
s_tx.getRange(2, 1, r, c).setValues(arr)
}
}
// Morphs a 1-d array into a 2-d array for use with Range.setValues([][])
// https://stackoverflow.com/questions/40552779/how-to-parse-json-response-in-google-app-script
function morphIntoMatrix(array) {
// Create a new array and set the first row of that array to be the original array
// This is a sloppy workaround to "morphing" a 1-d array into a 2-d array
var matrix = new Array();
matrix[0] = array;
// "Sanitize" the array by erasing null/"null" values with an empty string ""
for (var i = 0; i < matrix.length; i ++) {
for (var j = 0; j < matrix[i].length; j ++) {
if (matrix[i][j] == null || matrix[i][j] == "null") {
matrix[i][j] = "";
}
}
}
return matrix;
}