import Trading212 contract note statements to a google sheet using Apps script

T212_Transactions_importer.js

// reference: https://community.trading212.com/t/t212-contract-note-email-importer/15954/28

const config = {
  sheetName: 'Transactions',
  label: 'Trading212/Contract-Statements',
  mapTransactions: true,
};

const headerRow = [
  'Instrument',
  'ISIN',
  'Direction',
  'Quantity',
  'Price',
  'Total amount',
  'Trading day',
  'Trading time',
  'FX Fee',
  'Regulatory & Exchange Fees',
  'Order Type',
  'Execution venue',
  'Exchange rate',
  'Total cost',
];

const currDocument = SpreadsheetApp.getActiveSpreadsheet();
const sheet = currDocument.getSheetByName(config.sheetName);

function parseInstrument(input) {
  const sp = input.split('/');
  return [sp[0], sp[1]];
}

const columnMap = [
  [false, 'na'], // 0
  [false, 'na'], // 1
  [true, parseInstrument], // 2 0,1
  [true, (i) => i], // 3 2 direction
  [true, (i) => i], // 4 3 quantity
  [true, parseFloat], // 5 4 price
  [true, parseFloat], // 6 5 Total Amount
  [true, (i) => i], // 7 6 Trading day
  [true, (i) => i], // 8 7 Trading time
  [true, parseFloat], // 9 8 FX Fee
  [true, parseFloat], // 10 9 Regulatory & Exchange Fees
  [true, (i) => i], // 11 order type
  [true, (i) => i], // 12 execution venue
  [true, parseFloat], // 13 10 exchange rate
  [true, parseFloat], // 14 11 Total cost
];

function mapTransactions(rows) {
  const mappedRows = rows.map((row) => {
    const newRow = [];
    row.forEach((cell, i) => {
      if (columnMap[i][0]) {
        const generated = columnMap[i][1](cell);
        Array.isArray(generated)
          ? newRow.push(...generated)
          : newRow.push(generated);
      }
    });
    return newRow;
  });
  return mappedRows;
}

function getEmails() {
  const label = GmailApp.getUserLabelByName(config.label);
  const threads = label.getThreads();
  threads.forEach((thread) => {
    const messages = thread.getMessages();
    messages.forEach((message) => {
      if (message.isUnread()) {
        // <- will only import unread messages, remove this if you want to import all
        process(message);
        message.markRead();
      }
    });
  });
}

function process(message) {
  const body = message.getPlainBody();
  const firstElement = '№';
  const lastColumnElement = 'Total cost';
  const lastElement = "* All transactions' execution times are GMT.";

  let tableElements = [];
  let isRelevant = false;
  let firstColIndex = 0;
  let lastColIndex = 0;

  body.split('\n').forEach((el, i) => {
    const trimmedEl = el.trim();
    // conditions order is important
    if (trimmedEl !== '') {
      if (trimmedEl === firstElement) {
        firstColIndex = i;
        isRelevant = true;
      }
      if (trimmedEl === lastColumnElement) lastColIndex = i;
      if (trimmedEl === lastElement) isRelevant = false;
      if (isRelevant) tableElements.push(trimmedEl);
    }
  });

  const chunkSize = lastColIndex - firstColIndex + 1;

  const rows = [];
  for (let i = 0; i < tableElements.length; i += chunkSize) {
    const chunk = tableElements.slice(i, i + chunkSize);
    rows.push(chunk);
  }

  if (tableElements.length !== rows.length * chunkSize)
    console.warn({
      chunkSize,
      tableElementsLength: tableElements.length,
      rowsLength: rows.length,
      tableElements: tableElements,
    });

  if (config.mapTransactions) {
    if (sheet.getLastRow() === 0) sheet.appendRow(headerRow);
    mapTransactions(rows.slice(1)).forEach((row) => sheet.appendRow(row));
  } else {
    if (sheet.getLastRow() === 0) sheet.appendRow(rows[0]);
    rows.slice(1).forEach((row) => sheet.appendRow(row));
  }
}

function onOpen() {
  const entries = [
    {
      name: 'T212 Contracts',
      functionName: 'getEmails',
    },
  ];
  currDocument.addMenu('Import', entries);
}