Summing Table Rows by Condition with TamperMonkey Javascript

this is more just braindump notes,

we have a fancypants website for our apartment where we pay rent, so we are actually able to independently pay our parts of the rent on the website. this however led to a build up of confusion over time.

the table is structured like a balance sheet, so yuo can paste it into google docs. and the easy thing to do is to just add it up per person using the SUMIF function.

=-1 * SUMIF(B13:B110, "*2585*", C13:C110)
=-1 * SUMIF(B13:B110, "*Ondrej*", C13:C110)
=-1* SUMIF(B13:B110, "*Arianna*", C13:C110)
= SUMIF(C13:C110, ">0", C13:C110)

in English it is

“for each row, if column B contains the 2585 then add column C to sum”.

Oh, for the final one is, The Charges are + so sum those. (what we’ve paid is -) –> this conversion, from the $(xyz) to +/- format, is done automatically when pasted into google sheets.

querySelectorAll.forEach

find the table

The core is the function filterAndSum(). Specifically, I select all table rows that are not “hidden”. I use the “querySelectorAll” function.

        var rows = document.querySelectorAll('tr:not(.hidden)');

(Note: I noticed actually each row is repeated twice, so I had to filter to only select rows that did NOT have a “hidden” in their class.)

This returns an array which I iterate through using a for loop.

        for (let i=0; i <rows.length; i++) {

For each row in the array: If the text string in that table row contains e.g. my name,

            if (row.textContent.includes("orangenarwhals")) {

then I look for the $$$ in that row. So I use another querySelector, and look at table-data, or “td”, with class “mat-column-Amount”. In the below code, that’s what the “period” is for, it is a CSS class selctor. (see examples at https://developer.mozilla.org/en-US/docs/Web/CSS/CSS_Selectors e.g. you can specific child with “>”)

 row.querySelector('td.mat-column-Amount').textContent;

Then I used some copy-pasta to remove the $, (), and the comma from the string, e.g. $(1,234.53) turns into 1234.53

var punctuation = '$,()';
var regex = new RegExp('[' + punctuation + ']', 'g');
some_string.replace(regex, '')

Then I can use the built-in parseFloat to get a float, which I can them sum.

parseFloat

So the whole function looks like this.

function filterAndSum(str_filter) {
        var punctuation = '($,)';
        var regex = new RegExp('[' + punctuation + ']', 'g');
        //console.log(row.textContent);
        var rows = document.querySelectorAll('tr:not(.hidden)');
        var sum = 0;

        for (let i=0; i <rows.length; i++) {
            var row = rows[i];
            if (row.textContent.includes(str_filter)) {
                var str_amt = row.querySelector('td.mat-column-Amount').textContent;
                var amt = parseFloat(str_amt.replace(regex, ''));
                console.log('amount: ', amt);
                sum += amt;
            }
        }
        console.log('if filter by string', str_filter, 'there is sum', sum);
        return sum;
}

click

The other problem to solve is. That only a few months are revealed at first, so have to click “show more” button. So I can use the built-in click() to do so.

            document.querySelector('div.data-grid__show-more-container').childNodes[0].click();

This is the html

<div class="data-grid__show-more-container ng-star-inserted">
    <a bdi18n="Global.Component.Grid.ShowMore">Show more</a>
</div>

And to keep clicking until there’s no more to click..

function clickMores() {
    try{
        while (true){
            document.querySelector('div.data-grid__show-more-container').childNodes[0].click();
        }
    }
    catch (e) {
        console.log('done clicking "Show Mores"');
    }
}

Later on I added a sleep function, to allow for page load issues. This is from the internet, titled “ES6 vanilla sleep” or something like that. I guess ES6 is the latest-er version of javascript.


function sleep(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
}

Which then can be used (note “async”) like so:

async function clickMores() {
    try{
    while (true){
        document.querySelector('div.data-grid__show-more-container').childNodes[0].click();
        await sleep(1000);
    }

Show the information: make a div

then for the lulz I wanted to show the information. So create a div. Add text. Style. Figure out where to put it.

    var my_div = document.createElement('div');
    my_div.innerHTML = 'Please wait 10 seconds for the magic to happen :)';
    my_div.setAttribute("style", "font-size: 35px; color:red;");
    document.querySelector('body').prepend(my_div);

Later we can clean up this div

my_div.setAttribute('style', "visibility: hidden;");

templates with backticks

so i think python might have copied this from javascript… you can put expressions inside a string. just use backticks instead of quotes.

my_info_div.innerHTML = `Cool Info: Sum for N is ${filterAndSum('orangenarwhals')}`

all together

// ==UserScript==
// @name         New Userscript
// @namespace    http://tampermonkey.net/
// @version      0.1
// @description  try to take over the world!
// @author       You
// @match        https://bradleyproperties.managebuilding.com/Resident/portal/payments
// @icon         https://www.google.com/s2/favicons?sz=64&domain=managebuilding.com
// @grant        none
// ==/UserScript==


async function clickMores() {
    console.log('trying to click');
    try{
        while (true){
            console.log('clicking');
            document.querySelector('div.data-grid__show-more-container').childNodes[0].click();
            await sleep(1000);
        }
    }
    catch (e) {
        console.log('done clicking "Show Mores"');
    }

}

function filterAndSum(str_filter) {
        var punctuation = '($,)';
        var regex = new RegExp('[' + punctuation + ']', 'g');
        //console.log(row.textContent);
        var rows = document.querySelectorAll('tr:not(.hidden)');
        var sum = 0;

        for (let i=0; i <rows.length; i++) {
            var row = rows[i];
            if (row.textContent.includes(str_filter)) {
                //console.log('found string', row.textContent);
                var str_amt = row.querySelector('td.mat-column-Amount').textContent;
                var amt = parseFloat(str_amt.replace(regex, ''));
                console.log('amount: ', amt);
                sum += amt;
            }
        }
        console.log('if filter by string', str_filter, 'there is sum', sum);
        return sum;
}


function sleep(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
}

(async function() {
    console.log('hi');
    'use strict';

    var my_div = document.createElement('div');
    my_div.innerHTML = 'Please wait 10 seconds for the magic to happen :)';
    my_div.setAttribute("style", "font-size: 35px; color:red;");
    document.querySelector('body').prepend(my_div);


    console.log('Taking an 4 sec break...');
    await sleep(4000);
    console.log('4 seconds later...');


    my_div.innerHTML = 'Please wait for the magic to happen, we are clicking the page for you :)';
    my_div.setAttribute("style", "font-size: 35px; color:red;");
    document.querySelector('table').parentElement.prepend(my_div);


    clickMores();

    await sleep(2500); // i guess must wait for the clicks if they're asynchronous?


    var charges = filterAndSum('Charge');

    var expect_A = charges / 3.5 * .5;
    var expect_B = charges / 3.5; 
    var expect_C = charges / 3.5 * 2;

    var AAA = filterAndSum('nameA');
    var BBB = filterAndSum('nameB');
    var CCC = filterAndSum('nameC');

    var my_info_div = document.createElement('div');

    my_info_div.innerHTML = `Cool Info:
<br>  Sum for A is ${AAA}
<br>  Sum for B is ${BBB}
<br>  Sum for C is ${CCC}
<br>
<br>  Total Charges are ${charges}
<br>  Total Paid is  ${AAA+BBB+CCC}
<br>  Diff is ${charges - (AAA+BBB+CCC)}
<br>  ============================
<br>  Approx. Expectations (note that we are rounding to cents so won't quite match),
<br>
<br>  A: ${expect_A}, Diff is ${expect_A - AAA}, aka ${Math.round(expect_A - AAA)}
<br>  B: ${expect_B}, Diff is ${expect_B - BBB}, aka ${Math.round(expect_B - BBB)}
<br>  C: ${expect_C}, Diff is ${expect_C - CCC}, aka ${Math.round(expect_C - CCC)}
  `;
    my_info_div.setAttribute("style", "font-size: 35px; color:blue;");
    document.querySelector('table').parentElement.prepend(my_info_div);
    my_div.setAttribute('style', "visibility: hidden;");

}

Misc. Notes

IKEA - get all URLs on shopping cart
document.querySelectorAll("[data-testid=product_name]").forEach(function getHref(result){
console.log(`${result.childNodes[0].href}`);
});

XPath

    xpath=//div[contains(text(), "orangenarwhals")]/..//following-sibling::td[contains(@class, "mat-column-Amount")]/b/text()
    amts_iterator = document.evaluate(xpath, document.body, null, XPathResult.ANY_TYPE,
            null);

    var amt = amts_iterator.iterateNext();

    while (amt) {
        sum += blahblah(amt);
        amt = amts_iterator.iterateNext();
    };

http://ponderer.org/download/xpath/

https://scrapfly.io/blog/parsing-html-with-xpath/

https://jsfiddle.net/34wf5gzs/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.