function pullTextReportData() {
// Config
const SHEET_NAME = "Text report";
const DAYS_BACK = 30; // how many days back (including today)
const METRIC_MAP = {
Date: "__DATE__",
"Average response time (s)": "chats/response_time.response_time",
"Chatting duration": "chats/duration.agents_chatting_duration",
"Chat count": "chats/total_chats.total",
"Agent hours": "agents/availability.hours",
"Bad rates": "chats/ratings.bad",
};
// Authorization
const props = PropertiesService.getScriptProperties();
const BASIC_AUTH = props.getProperty("base64token"); // base64 of "accountId:PAT"
if (!BASIC_AUTH) throw new Error("Add token to Script properties.");
const headers = {
Authorization: "Basic " + BASIC_AUTH,
"Content-Type": "application/json",
};
// Dates
const today = new Date();
const from = new Date(today);
from.setDate(from.getDate() - (DAYS_BACK - 1));
const fromISO = isoDay(from, "00:00:00Z");
const toISO = isoDay(today, "23:59:59Z");
const allDates = listDates(from, today);
// Build metric requests
const base = "https://api.livechatinc.com/v3.6/reports/";
const metrics = Object.entries(METRIC_MAP)
.filter(([_, path]) => path !== "__DATE__")
.map(([col, path]) => {
const [endpoint, ...pathParts] = path.split(".");
return { col, endpoint, pathParts };
});
// Fetch each endpoint
const body = JSON.stringify({
distribution: "day",
filters: { from: fromISO, to: toISO },
});
const byMetric = {};
metrics.forEach((m) => {
try {
const resp = UrlFetchApp.fetch(base + m.endpoint, {
method: "post",
headers,
payload: body,
muteHttpExceptions: true,
});
if (resp.getResponseCode() !== 200) return;
const json = JSON.parse(resp.getContentText());
const recs = json && json.records ? json.records : {};
byMetric[m.col] = {};
Object.keys(recs).forEach((date) => {
let v = recs[date];
for (const k of m.pathParts) {
if (v && typeof v === "object") v = v[k];
else {
v = undefined;
break;
}
}
if (v !== undefined) byMetric[m.col][date] = v;
});
} catch (e) {
// skip on error; keep going
}
});
// Build table
const headersRow = Object.keys(METRIC_MAP);
const rows = allDates.map((d) =>
headersRow.map((h) => {
if (METRIC_MAP[h] === "__DATE__") return d;
const m = byMetric[h];
return m && m[d] !== undefined ? m[d] : "";
}),
);
// Write sheet
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME);
sh.clearContents();
sh.getRange(1, 1, 1, headersRow.length).setValues([headersRow]);
if (rows.length)
sh.getRange(2, 1, rows.length, headersRow.length).setValues(rows);
sh.setFrozenRows(1);
sh.autoResizeColumns(1, headersRow.length);
function isoDay(d, tail) {
const yyyy_mm_dd = new Date(d.getTime() - d.getTimezoneOffset() * 60000)
.toISOString()
.slice(0, 10);
return `${yyyy_mm_dd}T${tail}`;
}
function listDates(start, end) {
const out = [];
const cur = new Date(
Date.UTC(start.getUTCFullYear(), start.getUTCMonth(), start.getUTCDate()),
);
const stop = new Date(
Date.UTC(end.getUTCFullYear(), end.getUTCMonth(), end.getUTCDate()),
);
while (cur <= stop) {
const y = cur.getUTCFullYear(),
m = String(cur.getUTCMonth() + 1).padStart(2, "0"),
d = String(cur.getUTCDate()).padStart(2, "0");
out.push(`${y}-${m}-${d}`);
cur.setUTCDate(cur.getUTCDate() + 1);
}
return out;
}
}
