I want to extract data report from Kickserv into Google sheets, I have been able to get data by making a fetch request and formatting the HTML content. The code looks like this:
function processHtmlData() {
var baseUrl = 'https://app.kickserv.com/c4134e/reports/259901';
var totalPages = 3
//getTotalPages(baseUrl);
var allRows = [];
for (var page = 1; page <= totalPages; page++) {
console.log("Pages Processed: " + page)
var url = page === 1 ? baseUrl : baseUrl + '?page=' + page;
var options = {
'method': 'get',
'headers': {
'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
'accept-encoding': 'gzip, deflate, br, zstd',
'accept-language': 'en-US,en;q=0.9,de;q=0.8,it;q=0.7,sv;q=0.6',
'cache-control': 'no-cache',
'cookie': 'remember_employee_token= #########',
'pragma': 'no-cache',
'referer': 'https://app.kickserv.com/c4134e/reports',
},
'muteHttpExceptions': true // This will return the response even if it's an error
};
var htmlContent = UrlFetchApp.fetch(url, options).getContentText();
formatHTML (htmlContent); //function to format data
console.log(htmlContent)
}
This remember_employee_token
is used to get data, but the issue is it expires after some time. I am trying to make a request to get this token but could not able to get this specific parameter. I tried this, but it only gets _kickserv_secure_session
:
function fetchUrlAndLogCookies() {
// URL of the resource you want to request
var url = 'https://app.kickserv.com/c4134e/login';
// Options for the fetch request
var options = {
'method': 'get',
'muteHttpExceptions': true // To handle non-2xx status codes
};
// Perform the HTTP GET request
var response = UrlFetchApp.fetch(url, options);
console.log(response.getAllHeaders());
// Extract and log cookies from the response headers
var cookies = response.getHeaders()['Set-Cookie'];
console.log(cookies)
}
The output looks like this:
{ 'Set-Cookie': '_kickserv_secure_session=dE80eGlGUWw2dVJXSVVjaHVrcDNNdXp2T21IYUtINC83WGdlR0J4bzdWUm5xT0c1M0hyd2tuNGF2QUpTOC8wMUFmei9SUEhSd0cwbldVb1FpeEptNUkwbU1xWFZVK3gzdEh5Z2NUanVVcjNYSmtEY2EwZmRmMFJyMGo5WE9DVjFLNDY1N25nbHkyNTkxT3Z3UXpJbjJSbkQ0SHFkYmJPcjkzV1hKQ1I5UDhib0prOTg3cVE0RC81bTBpWDlTeXpmZ3JNdkc1ODlMQVFBUkpLM205RWdNUjBGc1pJWitOdlV3SUQ3dnlvMlptN1M3dFN3S3UxaUplWjBIWEdjdTdzWTlTbFB4cXUwdGcvVzBKYkF4RE1NaHpwK3BzRjRZaldKdW90Z2RFTVRBOU9Bc053ekhyTHFzdTVHUTBTYkd2cm5EZ3ozby9VMGVpaVZpMkVtY3Zndlh3PT0tLTQvcFE5WTFzbTE4bkxIS3dhSkdqUUE9PQ%3D%3D--51b5f191be55c9252ae98f241fe83235a6c21f2f; path=/; secure; HttpOnly; SameSite=Lax',
Date: 'Thu, 19 Sep 2024 09:48:05 GMT',
Connection: 'keep-alive',
'X-Frame-Options': 'SAMEORIGIN',
'x-permitted-cross-domain-policies': 'none',
Vary: 'Accept-Encoding',
'X-XSS-Protection': '0',
'Strict-Transport-Security':
[ 'max-age=63072000; includeSubDomains',
'max-age=31536000; includeSubDomains; preload' ],
'Content-Encoding': 'gzip',
Pragma: 'no-cache',
ETag: 'W/"525ae00a282d49b6702ef1bbf57f7a12"',
'X-Request-ID': 'e680bb47-fc69-4ed6-abba-3ae87865f228',
Link: '<https://cdn.kickserv.com/assets/application-8adc73f33afae63678d3b817ef5cc12de0bd3ac805bbe8e808b9a1fe9d0cb036.css>; rel=preload; as=style; nopush,<https://cdn.kickserv.com/assets/print-895c111bb23a33b2de77bbaea6b4706bb506d34590a8de2f8a65db019c04b6ba.css>; rel=preload; as=style; nopush,<https://cdn.kickserv.com/assets/vendor/bootstrap/respond-f3bc406d2a18a294fe7fcb67561e040ebfbeacdfa0316056b2cdc2d6c7c9c2c9.js>; rel=preload; as=script; nopush,<https://cdn.kickserv.com/assets/application-25f877d2bbbb2f7dcd5216879bdf8b4db1ca6a36b2319c296f37c86b50830977.js>; rel=preload; as=script; nopush',
'X-Download-Options': 'noopen',
'X-Content-Type-Options': 'nosniff',
Server: 'nginx',
'Transfer-Encoding': 'chunked',
'Content-Type': 'text/html; charset=utf-8',
'Cache-Control': 'no-store',
'Referrer-Policy': 'strict-origin-when-cross-origin',
Expires: 'Fri, 01 Jan 1990 00:00:00 GMT',
'x-runtime': '0.052074' }
When I see the login page, it is there, but it does not show up in the Headers:
Any guidance to get this parameter would be much appreciated.
You can get the remember_employee_token
by replicating the login request like this:
function getRememberEmployeeToken(username, password, account_slug = 'c4134e') {
const url = `https://app.kickserv.com/${account_slug}/login`;
let response = UrlFetchApp.fetch(url);
const html = response.getContentText();
const csrf_token = html.match(/name="csrf-token" content="(\S+)"/)[1];
const account_id = html.match(/"current_account":{"id":(\d+),/)[1];
const cookie = response.getHeaders()['Set-Cookie'].split(';')[0];
data = {
'authenticity_token': csrf_token,
'employee[username]': username,
'employee[password]': password,
'employee[account_id]': account_id,
'employee[remember_me]': '1',
'commit': 'Log in'
}
const options = {
method: 'post',
headers: { cookie },
payload: data,
followRedirects: false
};
response = UrlFetchApp.fetch(url, options);
let cookies = Object.fromEntries(response.getAllHeaders()['Set-Cookie'].map(c => c.split(';')[0].split('=')));
return cookies.remember_employee_token;
}