Извлечь данные Keenio в электронную таблицу Google

В настоящее время я использую ImportJSON для импорта электронной почты Sendgrid с URL-адресом API-интерфейса запроса данных Keenio, вызывая функцию ImportJSON в ячейке электронной таблицы Sheet DATA.

=ImportJSON("https://api.keen.io/3.0/projects/"& PROJECT_KEY & "/queries/extraction?api_key=" & API_KEY & "&event_collection=" & EVT_COL & "&timezone=" & TIMEZONE & "&latest=" &  LATEST & "&property_names..........", PTDATA!$AB$1)

В листе PTDATA в последней ячейке столбца я устанавливаю случайное число для пересчета ImportJSON. Функция запускается по событию открытия электронной таблицы. Я также добавил пользовательское меню для вызова пользовательской функции ReCalcCell.

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  // Or DocumentApp or FormApp.
  ui.createMenu('IMPORT DATA')
  .addItem('KEENIO DATA', 'ReCalcCell')
  .addToUi();
}


function ReCalcCell(){
  var min = Math.ceil(0);
  var max = Math.floor(9999);
  var randomNum = Math.floor(Math.random() * (max - min + 1)) + min 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("PTDATA");
  sh.getRange("$AB$1").setValue(randomNum);
}

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

Поскольку столбцы в листе DATA всегда появляются в случайном / случайном порядке, мне пришлось написать небольшую пользовательскую функцию GCL, которая принимает имя заголовка и возвращает свой адрес области данных из листа DATA в виде строки.

function GCL(header,dummy) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DATA");
  var headings = sheet.getRange(1, 1, 1, sheet.getLastColumn()); // get the range representing the whole sheet
  var width = headings.getWidth();
  var lrow = sheet.getLastRow();

    // search every cell in row 1 from A1 till the last column
    for (var i = 1; i <= width; i++) {
        var data = headings.getCell(1,i).getValue();
        if (data == header) {
          return ((sheet.getSheetName() + "!" + columnToLetter(i)+"2:" + columnToLetter(i) + lrow).toString()); // return the column range if we find it
           break; // exit when found
        }
    }
    return(-1); // return -1 if it doesn't exist
}

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

Затем я использую пользовательскую функцию GCL в каждом конкретном столбце, чтобы получить его диапазон данных. Когда данные заполнены, лист PDATA используется для создания различных сводок для целей отчетности.

= ARRAYFORMULA (ДВССЫЛ (ВКТ (А1,$AB$1)))

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

Техническая спецификация:

  • Столбцы отображаются перетасованными каждый раз, поэтому мои столбцы вычислений не могут рассчитываться, когда ссылки исчезают. Это делает стержни бесполезными! Чтобы противостоять этой проблеме, я должен был создать лист PDATA, чтобы тянуть в определенных столбцах, используя пользовательскую функцию GCL.
  • Пользовательская функция GCL не всегда обновляется и большую часть времени показывает ошибку #Ref.

PDATA Sheet:

Кстати, мой вывод JSON из Keenio выглядит так:

{
"result":
[
{
"sg_event_id": "92-OndRfTs6fZjNdHWzLBw",
"timestamp": 1529618395,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:59:55.000Z",
"created_at": "2018-06-21T22:00:28.532Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 38,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "no.name@noname.com"
}, {
"sg_event_id": "bjMlfsSfRyuXEVy8LndsYA",
"timestamp": 1529618349,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:59:09.000Z",
"created_at": "2018-06-21T21:59:39.491Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 36,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "no.name@noname.com"
}, {
"sg_event_id": "fru_s2s1RtueuqBMNoIoTg",
"timestamp": 1529618255,
"url": "https://noname.com?utm_campaign=website&utm_source=sendgrid.com&utm_medium=email",
"ip": "192.168.1.1",
"event": "click",
"keen": {
"timestamp": "2018-06-21T21:57:35.000Z",
"created_at": "2018-06-21T21:58:20.374Z",
"id": "555c1f7c5asdf7000167d87b"
},
"url_offset": {
"index": 29,
"type": "text"
},
"sg_message_id": "F5mwV1rESdyKFA_2bn1IEQ.filter0042p3las1-15933-5B2A68E8-36.0",
"useragent": "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)",
"email": "no.name@noname.com"
}
]
}

Мои вопросы:

  • Есть ли способ проанализировать результат JSON без использования ImportJSON, который должен быть введен как пользовательская функция в ячейке, которая также зависит от пересчета? ImportJSON иногда не работает должным образом.
  • Как можно реорганизовать или оптимизировать этот код, чтобы он всегда мог возвращать данные в столбцы таблицы PDATA?
  • Есть ли лучший способ выполнить то, что я хочу, не прибегая к пользовательским функциям, таким как GCL в листе PDATA или ImportJSON в листе данных?
Источник

2 ответа

Решение

Как насчет этого примера сценария? Этот скрипт анализирует значения, полученные из API с помощью UrlFetchApp, и помещает их на лист "DATA". Вы можете запустить это в меню электронной таблицы. Прежде чем запустить это, пожалуйста, поставьте конечную точку.

Пример скрипта:

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  // Or DocumentApp or FormApp.
  ui.createMenu('IMPORT DATA')
  .addItem('KEENIO DATA', 'ReCalcCell')
  .addItem('main', 'main')
  .addToUi();
}

function main() {
  var url = "###"; // Please put the endpoint with your token.

  var res = UrlFetchApp.fetch(url).getContentText(); // Modified
  var values = JSON.parse(res);
  var putData = values.result.map(function(e) {return [e.useragent, e.sg_event_id, e.timestamp, e.ip, e.url, e.event, e.keen.timestamp, e.keen.created_at, e.keen.id, e.url_offset.index, e.url_offset.type, e.sg_message_id, e.email]});
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DATA");
  sheet.getRange(sheet.getLastRow() + 1, 1, putData.length, putData[0].length).setValues(putData);
}

Замечания:

  • Когда вы используете это, пожалуйста, укажите конечную точку, включая ваш токен, url,
  • Я подтвердил этот скрипт, используя объект JSON в вашем вопросе. Поэтому, если структура объекта изменяется, необходимо также изменить скрипт. Пожалуйста, будьте осторожны с этим.

Ссылка:

Если я неправильно понимаю вашу проблему, пожалуйста, сообщите мне. Я хотел бы изменить это.

Изменить 1:

Образец 1:

var putData = values.result.map(function(e) {return [e.useragent, e.sg_event_id, e.timestamp, e.ip, e.url, e.event, e.keen.timestamp, e.keen.created_at, e.keen.id, JSON.parse(e["url_offset"]).index, JSON.parse(e["url_offset"]).type, e.sg_message_id, e.email]});

Образец 2:

var putData = values.result.map(function(e) {return [e.useragent, e.sg_event_id, e.timestamp, e.ip, e.url, e.event, e.keen.timestamp, e.keen.created_at, e.keen.id, e["url_offset"].index, e["url_offset"].type, e.sg_message_id, e.email]});

Изменить 2:

Не могли бы вы запустить этот скрипт и указать значения созданного файла? Конечно, пожалуйста, удалите личную информацию. Но, пожалуйста, не изменяйте структуру объекта. Если вы не можете сделать это, я хотел бы думать о других путях.

var url = "###"; // Please put the endpoint with your token.

var res = UrlFetchApp.fetch(url).getContentText();
DriveApp.createFile("sample.txt", res, MimeType.PLAIN_TEXT)

Изменить 3:

Пожалуйста, скопируйте и вставьте этот скрипт в ваш редактор скриптов, запустите myFunction(). Затем, пожалуйста, покажите значения файла. Когда вы запустите эту функцию, убедитесь, что в вашем проекте НЕ используется одно и то же имя функции.

function myFunction() {
  var url = "###"; // Please put the endpoint with your token.
  var res = UrlFetchApp.fetch(url).getContentText();
  DriveApp.createFile("sample.txt", res, MimeType.PLAIN_TEXT)
}

Изменить 4:

Пожалуйста, скопируйте и вставьте этот скрипт в ваш редактор скриптов, запустите myFunction2(). Тогда, пожалуйста, покажите результаты. Когда вы запустите эту функцию, убедитесь, что в вашем проекте НЕ используется одно и то же имя функции.

Пожалуйста, подтвердите, есть ли ключи и значения keen а также url_offset получены

function myFunction2() {
  var url = "###";
  var res = UrlFetchApp.fetch(url).getContentText();
  var values = JSON.parse(res);
  for (var key in values.result[0]) {
    Logger.log("key: %s, value: %s", key, values.result[0][key])
    if (typeof values.result[0][key] == "object") {
      for (var dkey in values.result[0][key]) {
        Logger.log("key: %s, dkey: %s, value: %s", key, dkey, values.result[0][key][dkey])
      }
    }
  }
}

Изменить 5:

Пожалуйста, скопируйте и вставьте этот скрипт в ваш редактор скриптов, запустите myFunction3(). Тогда, пожалуйста, покажите результаты. Когда вы запустите эту функцию, убедитесь, что в вашем проекте НЕ используется одно и то же имя функции.

function myFunction3() {
  var url = "###"; // Please set this.
  var res = UrlFetchApp.fetch(url).getContentText();
  var values = JSON.parse(res);
  var obj = [];
  for (var i = 0; i < values.result.length; i++) {
    var temp = {};
    var v = values.result[i];
    for (var key in v) {
      temp[key.replace(/_/g, "")] = v[key];
      if (typeof v[key] == "object") {
        for (var dkey in v[key]) {
          temp[key.replace(/_/g, "") + dkey.replace(/_/g, "")] = v[key][dkey];
        }
      }
    }
    obj.push(temp);
  }
  var putData = obj.map(function(e) {return [e.useragent, e.sgeventid, e.timestamp, e.ip, e.url, e.event, e.keentimestamp, e.keencreatedat, e.keenid, e.urloffsetindex, e.urloffsettype, e.sgmessageid, e.email]});
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("DATA");
  sheet.getRange(sheet.getLastRow() + 1, 1, putData.length, putData[0].length).setValues(putData);
}
Источник

Глядя на то, что вы делаете здесь, было бы гораздо проще разработать электронную таблицу в формате "только для добавления" с интеграцией Zapier.

Zapier может напрямую обрабатывать события SendGrid и добавлять эти события в вашу электронную таблицу, если вы этого хотите.

И тогда вы можете разместить свои "столбцы расчета" на отдельном листе в электронной таблице.

Просто идея.

Источник
Другие вопросы по тегам