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/

Pandemic Diary #86 – agkcwjglkd

when did i last even post about my life?

anyway, i’ll braindump the covid stuff (my time with covid), post covid stuff (party), roe v wade, idk what else

COVID — when I caught covid. that’s when the roe v wade decision came out. i couldn’t protest. was pretty stressful time.

tests. can get not just the free tests through usps, but also 8 free ones per month through insurance. if get them through pharmacy, they may be able to bill insurance directly so you don’t have to deal with reimbursement. also, a friend can pick it up for you potentially if they have all the information (insurance card and birthday etc.) and you are on the phone.

paxlovid is free through test-to-treat if you have some pre-existing conditions, nationwide.

https://aspr.hhs.gov/TestToTreat/Pages/process.aspx “Paxlovid is currently being used to treat mild-to-moderate COVID-19; the treatment costs $530 per person. However, it is being distributed for free by the U.S. federal government”

and MA has free telehealth, doesn’t interact with insurance. (but if you have insurance, it probably also provides free online apptmts)

https://www.mass.gov/info-details/free-telehealth-for-covid-19-treatment-with-paxlovid

and, they’re totally happy to have someone else pick the paxlovid script up for you (they can also mail it overnight)

was kinda curious a lot of mental health conditions are listed as risk factor

https://www.cdc.gov/coronavirus/2019-ncov/need-extra-precautions/people-with-medical-conditions.html

learned through researching that there is more linking physical and mental health than i thought (despite once writing an email citing articles about how laughter was good for your immune system! for SIGTBD)

mental health conditions don’t really exist as a separate thing from physical health conditions in many cases. like depression is associated with a low-level inflammatory response and increased metabolic stress which further develop the condition, especially in specific parts of the brain

do you know why covid can temporarily/permanently screw with your sense of taste and smell? that’s actually covid infecting the olfactory nerve and having integrated itself into the nervous system and causing inflammation. so if you already have a condition like depression the additional stressor of covid can cause even more inflammation or a chain reaction

and more scientifically

i thought it might be mostly social stuff, like bipolar = more like to be poor, less social support, etc. but from this JAMA paper we have both

“Mental health disorders are also associated with socioeconomic deprivation4 and reduced access to care,2 important factors of poor COVID-19 outcomes”

https://jamanetwork.com/journals/jamapsychiatry/fullarticle/2782457

and also this

“Variation in the human leukocyte antigen complex is one of the most consistently replicated findings in genome-wide association studies in patients with schizophrenia and bipolar disorders. Human leukocyte antigen predominantly regulates viral infection, especially COVID-19. Genetic variability across major human leukocyte antigen class I genes may contribute to differences in the immune response to COVID-19, and an inappropriate T-cell response has been implicated in severe COVID-19 outcomes. Abnormal cytokine levels have also been found in the cerebrospinal fluid of patients with schizophrenia and bipolar disorders, who are also at higher risk of hypovitaminosis D, contributing to poor COVID-19 prognosis

https://jamanetwork.com/journals/jamapsychiatry/fullarticle/2782457

was interesting inversion, cdc said okay to go out after 10 days but still have mental holdover of 14 days, go into stores wearing an n95 to protect other people. unexpectedly standing in checkout line with a crowd of people is less stressful.

tested daily, negative on friday morning, then positive on sat morning on cue. symptoms not obvious right when i woke up

during noon-ish telehealth could talk for a while but eventually cough a bit. perhaps the only real sign prior to positive test is the tickle in the back of my throat didn’t go away after two days.

telehealth answered questions / med interactions and script went to walgreens. called to check they had it, and they actually didn’t and had to go elsewhere.

really tired and slept most of two or three days, had a bit of wet cough (mucousy), throat got sore enough for a day to want cough drops but it was wincing not agony. but no symptoms bad enough to interfere with sleep. not sure if i ever had a fever, no thermometer

no symptoms, negative on sat evening and again on mon morning -> went to fourth of july o__o

on the whole, possibly thanks to paxlovid my symptoms were much better than for my normal cold — can’t stop coughing and sore throat hurts that swallowing is agony like swallowing needles, if you get up snot goes everywhere, and can’t sleep well

i had clear eligibility so I didn’t ask a lot of questions, my healthier friends asked more questions during telehealth

the most relevant thing would be if I were suffering right now, it’d ease my symptoms
the long covid stuff is mostly a lingering cough for a couple months, but that does go away
if I’m not having problems with the cough now (while immediately sick), anecdotally I should be in the clear for that

so, idk, who knows…

Pfizer PFE announced additional data from the phase II/III EPIC-SR study, which evaluated the use of its COVID-19 treatment pill Paxlovid in patients who are at standard risk for developing severe COVID-19 infection.
As was reported last year, the EPIC-SR study had failed to achieve its primary endpoint of self-reported, sustained alleviation of all symptoms for four consecutive days …
And updated study concurred for hospitalization and death

https://www.msn.com/en-us/health/medical/vaccinated-low-risk-people-won-t-benefit-from-covid-19-pill-paxlovid-pfizer-study/ar-AAYKm5v ttps://www.nasdaq.com/articles/pfizer-pfe-covid-pill-ineffective-in-standard-risk-patients

set up a whole kitchen in my room, then managed to drown my work laptop. hepa filtered and n95, somehow roommate sitting next to me in living room while I was eating didn’t catch it. stressful to communicate over text. still felt like a lot of bother even though on other side I knew I wouldn’t mind in the least grabbing chopsticks etc from the kitchen.

used one of the binax tests, was confusing at first. stuck my test to the table instead of sticking it closed on itself. lol.

post covid: time to kiss all the strangers! jkjk. but i did go clubbing! at club cafe. went to indoor brunch with a live band. wow, there are so many possibilities! room escape games, whale watch tours, live comedy shows..

i didn’t get around to a lot of it, but was still fun to come up with list of ideas

other stuff?

oh, went to acadia! (and tj maxx and walmart). good hikes. saw supermoonrise, milky way. played undertale, enjoyed. fixed fuse, brakers, shifter on my ebike, spray painted it, next step attach trailer hitch and go to costco (?!). was super stressed then less stressed about phd. still pondering startup life. roommate watercolor night. want to get isntant electric feedback, looking at cameras. bookbinding! and leatherworking. woodworking tbd.

reading effortless. still listening to npr indicator and shortwave. (oh, got a bunch of npr swag and a planet money comic!), the song freaking out.

monkeypox spreading through shirtless clubbing, fire island. the webb telescope. not making progress on side projects, maybe that’s okay. dreading the future but remembering that any particular state of life can change drastically in 3 or 4 years.

Somerville, MA: Saving Money on Electricity by Going Green in 5 minutes, no landlord approval needed

Overview: We will switch electricity supplier (but not distributor) from Eversource to Somerville Community Choice Electricity.

Note: The supplier switching happens behind the scenes by Eversource. The distributor doesn’t change and there’s no modification to the hardware at all. So you don’t need landlord permission, it all happens with the click of a button.

Again, just fill out the one-page form here:

https://cce.somervillema.gov/opt-up-or-join/

Took me 5 minutes total.
















Appendix

Prices In more detail

Eversource bill puts us at $0.158/kWh, or 15c / kWh. Somerville Community Choice puts us at

  • 13.2 c/kWh for 100% renewable !
  • Or 10.2c /kWh for basic (50% required by MA)
  • or 10.5c/kWh for 10% voluntary (60% total) more renewable.

So save money by switching to renewable energy. For 335 kWh the calculations:

  • $52.93 original
  • $34.17 SomervilleCCE
  • $44.22 SomervilleCCE, 100% renewable.

FAQ: What are costs? Supplier vs Delivery

Basically, half our bill is from electric supplier, which we can change at will / any time / with no fees. The other half is from our electric deliverer, which remains unchanged.

Screenshot from https://cce.somervillema.gov/

On the Eversource bill you can actually see this cost breakdown. Ours is about 50/50 supplier and delivery cost. This will change depending on how much electricity you use. The below example bill shows our rate as 15.8c/kWh.

Screenshot from Eversource electricity bill https://www.eversource.com/content/ema-c/residential/account-billing/view-pay-bill

FAQ: Look up suppliers by zip code

Massachusetts.gov made a cool site to look up suppliers (energyswitchma.gov). Here you can see for zip code 02143 the available choices of supplier, as well as their price.

Screenshot from https://www.energyswitchma.gov/#/compare/2/1/02143//

Step 1: Finding account information

It’s in the top right corner of your bill (probably on the second page).

Screenshot of electricity bill

Step 2: Fill out One-Page Form

Screenshot from https://cce.somervillema.gov/opt-up-or-join/

Step 3: Done!

Within a minute I got an email confirmation:

Screenshot of email confirmation

Enjoy the savings!

I’ll update this post in a month with our new energy bill. Let me know if you find this useful.

List of website URLs for more info



On a total tangent, Meanwhile… Will this choice be taken away?

Per this article from PV magazine, there is a bill introduced that would remove this right to switch suppliers!

http://pv-magazine-usa.com/2022/06/08/massachusetts-bill-bans-residents-from-choosing-clean-energy-suppliers/

This is the section of the bill

Wait, why remove consumer choice??

The argument for removing choice is that 3rd party suppliers are going door-to-door and actually scamming people / raising people’s rates, predominately low-income people, vs if they stuck with default utility rate.

Massachusetts residents who get their power through competitive electric suppliers paid $426 million more over five years than they would have if they’d stayed with their utility companies, according to a new report from Attorney General Maura Healey.

The report, prepared by the Susan M. Baldwin Consulting firm, found that low-income residents are nearly twice as likely to sign up with competitive electric suppliers and are also charged higher rates than other customers.

https://www.wbur.org/news/2021/04/02/competitive-electric-suppliers-overpayment-healey-report-bills

My 2c

But on the other hand, Eversource only offers 51% renewable as mandated by state. Whereas there are tons of 100% renewable energy plans on Mass Switch.

In my opinion seems like the choice shouldn’t be taken away ! but rather that bad faith actors should be fined and regulated.

More information

https://news.google.com/search?q=Massachusetts+S2842&hl=en-US&gl=US&ceid=US:en

 S.2842, includes a provision that would prevent retail suppliers from creating new contracts or renewing contracts after 2023. The provision comes without amendments from S.2150, a 2021 bill from state Sen. Brendan Crighton, D, that received support and testimony from Attorney General Maura Healey and then-state Energy and Environmental Affairs Secretary Kathleen Theoharides, among others

https://www.utilitydive.com/news/massachusetts-rollback-retail-customer-electricity-choice-bill/624968/

The overarching bill seems really cool. Here is it explained in plain english:

https://www.wbur.org/news/2022/04/08/massachusetts-2022-climate-bill-senate

Fulltext of relevant section of the bill = I’m confused

Full text: https://malegislature.gov/Bills/192/S2842.pdf

But, the text sounds like Somerville municipial energy would still be a choice?

SECTION 54.  Notwithstanding any general or special law to the contrary, beginning on January 1, 2023, no supplier as defined in section 1 of chapter 164 of the General Laws shall execute a new contract or renew an existing contract for generation services with any individual residential retail customer. This section shall not apply to, or otherwise affect, any government body that aggregates the load of residential retail customers as part of a municipal aggregation energy plan pursuant to section 134 of chapter 164 of the General Laws. A violation of this section shall be an unfair and deceptive act pursuant to chapter 93A of the General Laws and the attorney general may bring an action under section 4 of said chapter 93A to enforce this section and to seek restitution, civil penalties, injunctive relief and any other relief awarded pursuant to said chapter 93A.

https://malegislature.gov/Bills/192/S2842, page 34

So, how to comment or ask questions?

Email Senate Committee on Ways and Means, perhaps.

https://malegislature.gov/Committees/Detail/S30/192/Cosponsor

Or call them!

Or perhaps look at the sponsor of the original text, https://malegislature.gov/Bills/192/S2150 Sen. Brendan Crighton from Third Essex.

I think I (or my friends who vote in MA) will email and ask what this means for people in Somerville / on the Somerville Community Choice Electricity plan. And/or if there will continue to be 100% renewable options.

A funny 404 page

Oh my cod!