Как написать макрос в Google Sheet на примере парсинга транзакций с кошелька etherscan

от

Последние полгода я создаю достаточно много файлов и скриптов в 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. Упрощенная ссылка (без опциональных параметров) будет выглядеть таким образом:

https://api.etherscan.io/api?module=account&action=tokentx&address=0x31be3f5a0f2c0acacde1440e1196e78c37b938a3&apikey=49R5KT8FPEKFPV5DM1JMBTS978H72VEEB1

Если открыть ее в браузере — результатом будет много буковок, которые при ближайшем рассмотрении окажутся 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;
}

Оставьте комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.