Advent of Code w/ AppsScripts

[Adam Morris]

Hi all,

When and where I can, I'll be trying to complete the advent of code challenges in appscripts. You can follow along here in this project:

https://script.google.com/home/projects/1nbIXikKL7P6Ibayam8JrTA8UPaudcyuYzOwe_mEPLEwvVHwhiKeDIlEN/

I find these sorts of exercises really valuable. It makes you think about the language you're using very carefully. In today's installment, I had to figure out how to take an array and break them up into all the various combinations.

While I love appscripts for how easy it is to interact with the services, I really enjoy using the platform for more CSy stuff as well.

Adam

[Emeric HOCHART]

My solution for Star 1 :

Open google sheet

Column A : puzzle input

Column B : formula =2020-Ai

Column C : formula = VLOOKUP(Ai;A:A;1;False)

Emeric Hochart

Ingénieur S.I.

+33 620582456

JETLANE SAS - AML - DECATHLON

Site du B'twin Village

4 rue du Professeur Langevin 

59000 - LILLE

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHQVb%2BTHr2N9ryMECtV4qxK8Pnx-uYtMu1CEQa-m_gqL_8NxTg%40mail.gmail.com.

[Emeric HOCHART]

oups 

Column C : formula = VLOOKUP(Bi;A:A;1;False)  

Emeric Hochart

Ingénieur S.I.

+33 620582456

JETLANE SAS - AML - DECATHLON

Site du B'twin Village

4 rue du Professeur Langevin 

59000 - LILLE

[Kim Nilsson]

Cool! I'm going to follow your progress.

I'll probably learn lots. :-)

I think there's a typo in your star2; the 1 at the end should be a 2.

Logger.log(result[0][0] * result[0][1] * result[0][2])

[Kim Nilsson]

@Emeric, I did a variant of your suggestion for Star 1.

Column B: formula = ARRAYFORMULA(2020-A1:A)

Column C: formula = ARRAYFORMULA(IFERROR(VLOOKUP(B1:B;A:A;1;False);""))

Column D: formula = PRODUCT(C:C)

Alternative one-liner in B1.

=PRODUCT(ARRAYFORMULA(IFERROR(VLOOKUP(ARRAYFORMULA(2020-A1:A) ;A:A;1;False);""))  )  

/Kim

[Emeric HOCHART]

+1

Emeric Hochart

Ingénieur S.I.

+33 620582456

JETLANE SAS - AML - DECATHLON

Site du B'twin Village

4 rue du Professeur Langevin 

59000 - LILLE

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2f708b3d-8b4a-4014-b9a4-6957bf540e09n%40googlegroups.com.

[Adam Morris]

Great stuff guys. 

Updated project to solve Day 2 now. I prefer to use Array methods rather than for loops, as you can probably tell. To me they seem so much more expressive. (Also more fun to type.)

https://script.google.com/home/projects/1nbIXikKL7P6Ibayam8JrTA8UPaudcyuYzOwe_mEPLEwvVHwhiKeDIlEN/

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/2f708b3d-8b4a-4014-b9a4-6957bf540e09n%40googlegroups.com.

[Kim Nilsson]

My 14yo son solved it in python first with for-loops, and then I showed him Emeric's and my solutions in Google Sheets, and told him he could probably convert his python code to GAS. So he did, with a little help from me, as python is more direct, and he needed a few more var-lines (setting the values of a,b,c based off the x,y,z which apparently python doesn't need) with GAS.

  function myFunction() {

    var num = [His values here in a comma-separated list]

    for (var x = 0; x < num.length; x++) {

      var a = num[x]

      for (var y = 0; y < num.length; y++) {

        var b = num[y]

        for (var z = 0; z < num.length; z++) {

        var c = num[z] 

          var answer = a + b + c

          if (answer == 2020) {

          // console.log(a, b, c) // This logs the three values six times (all possible iterations)

          var values = [a, b, c]

          var result = a * b * c

          }

        }

      }

    }

    console.log(values)

    console.log(result)

  }

We then spent a while trying to grab the values from a single cell in the Sheet, instead of including it directly in the code, but something kept us from getting it right. Still, just seconds before heading to bed, we think we know how to solve it.

We had no problem with getValues, but I think we forgot that the result we got was a string, and we guessed that he needs it to be [string] for his loop to work. He'll try that when he gets home from school later today. :-)

Kim

[Adam Morris]

That’s awesome!

The guess is correct that what the data structure returned by getValues is the missing piece. It doesn’t return a string, nor an array of strings [string] but a 2D array with strings as the values [[string]]

Actually technically its [[object]] but for your purposes you can assume it’s [[string]]

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a5yD9UkUVhTXSkz9TGws_OwvHHE%3DTFcL10nB1mdRH2uXg%40mail.gmail.com.

--

http://classroomtechtools.com

[Kim Nilsson]

Aha, ok.

We'll see how he does.

We figured there must be something missing in the structure, as using setValue to push the getValue from D1 into D2 returned exactly the same information, so he was definitely getting the value of D1, but his for-loops gave no return.

Kim

[Kim Nilsson]

@Emeric, again I used Sheets to solve this. :-)

My input and result looks like this.

First round I did the "split text to columns" of the PuzzleInput manually, and then I realised I, of course, must do that with formulas too.

So these are the formulas of the columns going from left to right. US English must probably replace all my ; with , for the formulas to work.

O2: =ARRAYFORMULA(SPLIT(REGEXREPLACE(REGEXREPLACE(N2:N;":";"");"-";" ");" "))

S2: =ARRAYFORMULA("[^" & Q2:Q & "]")

T2: =ARRAYFORMULA(LEN(REGEXREPLACE(R2:R; S2:S; "")))

U2: =ARRAYFORMULA(IF(T2:T <= P2:P;IF(T2:T >= O2:O;1;0);0))

V2: =SUM(U2:U) which is the answer or Star 1

W2: =ARRAYFORMULA(MID(R2:R;O2:O;1))

X2: =ARRAYFORMULA(MID(R2:R;P2:P;1))

Y2: =ARRAYFORMULA(IF(Q2:Q=W2:W;1;IF(Q2:Q=X2:X;1;0)))

Z2: =ARRAYFORMULA(IF(Y2:Y=1;IF(W2:W=X2:X;0;1);0))

AA2: =SUM(Z2:Z) which is the answer or Star 2

Son is still working with his first part of Day 2. :-)

Just gave him a tip on how to separate his input with something like SPLIT and remove unnecessary characters.

He's attacking it with python again.

[Kim Nilsson]

Ah, it dropped the image.

[Adam Morris]

I think it's so interesting that even spreadsheets can be used to solve these problems.

Today's was a classic "line of sight" programming problem. I actually got the wrong answers quite a number of times, because of one-off errors. I also got stuck with a language thing that I keep making.

In Javascript, curly braces aren't needed for one line block statements. So for an if statement, like this, works as expected:

// if past the right boundary, go back to the left

if (x >= X)

x = x - X;

But when I was debugging or checking things, I wanted a logger statement to see what x was turned into when crossing a boundary, so I entered this:

if (x >= X)

x = x - X;

Logger.log('x is', x)

Whoops. That log statement is indented because I'm imagining the curly braces to be there when not, and so it's not going to do what I'm expecting it to do! 

On Thu, Dec 3, 2020 at 4:10 AM 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

Ah, it dropped the image.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1977654e-0e40-43e3-a108-973f03a8f023n%40googlegroups.com.

[Kim Nilsson]

Yeah, I saw my son getting slapped with indentation errors all the time, as he's using python. :-)

Kim

[Emeric HOCHART]

Ok my version for day1 star 1

const ss = SpreadsheetApp.openById('1JwmJj......sivU_BhZ6I');

function day1_star1() {

const ws = ss.getSheetByName('day1');
let input = ws.getDataRange().getValues().flat();

let answer = input.find(element=>input.includes(2020-element));

return answer*(2020-answer);
}

Emeric Hochart

Ingénieur S.I.

+33 620582456

JETLANE SAS - AML - DECATHLON

Site du B'twin Village

4 rue du Professeur Langevin 

59000 - LILLE

Le jeu. 3 déc. 2020 à 08:29, 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> a écrit :

Yeah, I saw my son getting slapped with indentation errors all the time, as he's using python. :-)

Kim

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a7ZDw3XFVzwJTdkDVNY91AV%3D06iC8Y%2By0%2B78fwCDjaY_w%40mail.gmail.com.

[Kim Nilsson]

That was very nice and short.

Kim

[Emeric HOCHART]

and star 2

function day1_star2() {

const ws = ss.getSheetByName('day1');
let input = ws.getDataRange().getValues().flat();

let transform = input.map(element=>2020-element);
let answer = transform.find(item=>input.find(element=>input.includes(item-element)));
let answer2 = input.find(element=>input.includes(answer-element));

return (2020-answer)*answer2*(answer-answer2);

}

Emeric Hochart

Ingénieur S.I.

+33 620582456

JETLANE SAS - AML - DECATHLON

Site du B'twin Village

4 rue du Professeur Langevin 

59000 - LILLE

Le jeu. 3 déc. 2020 à 14:39, 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> a écrit :

That was very nice and short.

Kim

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a612S0WzZbqaTtmnxByHa%3DM0-kzs2J_1YbsXN%2Bjtu8D9g%40mail.gmail.com.

[Kim Nilsson]

That's great, Emiric.

For a coding n00b like myself, would you please like to explain what your code does?

I only understand parts of it (the mathematics) but not the commands.

Kim

[Emeric HOCHART]

excellent
i don't look at the solution in this case and i will try to do it with gsheet

Emeric Hochart

Ingénieur S.I.

+33 620582456

JETLANE SAS - AML - DECATHLON

Site du B'twin Village

4 rue du Professeur Langevin 

59000 - LILLE

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/06ba1bd6-88cf-43ef-bcd2-e0caa77860f8n%40googlegroups.com.

[Emeric HOCHART]

i'm noob too

let input = ws.getDataRange().getValues().flat();

let transform = input.map(element=>2020-element);
let answer = transform.find(item=>input.find(element=>input.includes(item-element)));
let answer2 = input.find(element=>input.includes(answer-element));

return (2020-answer)*answer2*(answer-answer2);

flat : https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/flat

includes : https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/includes

find : https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/find

I reduce the problem to a problem identical to that of step "star 1" thanks to my array "transform".

Then for each value in the transform array, I use step Star 1 and test if there is a solution with the find method. 

As soon as I have found a solution, I retrieve the value and deduce the other 2 from it.

on the other hand it is not super optimized

Emeric Hochart

Ingénieur S.I.

+33 620582456

JETLANE SAS - AML - DECATHLON

Site du B'twin Village

4 rue du Professeur Langevin 

59000 - LILLE

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a5SZLYs3J7WMQ231qEOWibEtTA4C7d%2BYqqc3ingEUy_2Q%40mail.gmail.com.

[Kim Nilsson]

Thanks! 

[Jean-Luc Vanhulst]

Here's a day 3 version:

 var sheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("day 3")
  var range = sheet.getRange(1,1,sheet.getLastRow(),1);

  var values = range.getValues();
  values.forEach( (x,i) => { values[i] = [...x[0]];values[i].forEach( (y,i,a) => {if(y=="#") { a[i]=1 } else {a[i]=0}} ) } );
  var trees = 0;
  values.forEach( (x,i) => { trees += x[ (i*3) % x.length ] } );

  return trees

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

On Thu, Dec 3, 2020 at 11:15 AM 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

Thanks! 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACK38a5NYuxsh6WBcODy1pq%2ByqqA6MWdp3OnZ6XzHTj9nebBBw%40mail.gmail.com.

[Adam Morris]

Day 4 was tedious. A lot of validation checks. It's actually not entirely complete, there's an invalid case that my code is returning valid, and I've stared at the logs too long for one problem to find it.

In terms of how I like to solve things, the problem required a lot of "case by case" code. A lot of times, JavaScripters want to use the switch/case statement, but I always choose to use objects instead, like how Python would do it with dictionaries:

instead of this:

switch (variable) {

case 'hi':

return 'english';

case 'nihao':

return 'chinese';

default:

return 'dunno';

}

I prefer to do it this way:

const obj = {

'hi': 'english',

'nihao': 'chinese'

};

return obj[variable] || 'dunno';

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ50p8ff4M1CAtdPS3mwNADdS%3D4MpOn0gw_oKaQoZnOH8Rg%40mail.gmail.com.

[CBM Services]

On validations, do you have an easy quick way to validate a date? Ran into an issue yesterday where someone entered a date using Google Forms with the year as 20 rather than 2020. All the checks I had to ensure date is accurate were passing strangely. When I looked as the variable in the GAS debugger it told me date is invalid.

I put a check on the year so I can catch it. But the function getFullYear() was returning NaN instead if 20. So finally I just modified my check to look for NaN.

How the debugger was determining that the date value was invalid does not seem to be something that I can easily check myself through code.

I should probably enter an issue also against Google Forms for why they would even accept an invalid date entry to start. Or at least give us some ability in the entry to validate the entry.

---

From: Adam Morris
Sent: ‎2020-‎12-‎04 4:22 AM
To: google-apps-sc...@googlegroups.com
Subject: Re: [Apps-Script] Advent of Code w/ AppsScripts

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHQVb%2BT-DDpPt-wnxLF3seYQLhqopSGyrGqEPcATHVaBpPdp0w%40mail.gmail.com.

[Adam Morris]

Day 5 was more straightforward than yesterday. Two language items stood out.

When you perform a sort on an array in JavaScript, I keep forgetting that by default it converts all the items to strings and does it alphabetically. But for this problem, I needed to arrange all the seat IDs by integer sort so that I could easily check whether they were occupied or not.

// sorting by default is alphabetical, so we need a callback that

// does not convert to a string by default

seats.sort((a, b) => a - b);

Other than that, I needed to go through all the possible seat IDs, except the first and last row. So I needed to make an array of numbers, which I could do in a for loop. There's another way to create an array of numbers pretty easily, but the mechanics of it are a bit odd. This makes an array from 0 to 19:

const nums = Array.from(Array(20).keys());

What's happening is that Array(20) makes 20 empty slots, and keys() will give us an array twenty nulls, and the Array.from knows how to take that list of nulls and convert it into sequential numbers. 

So to get a list from 8 to 1015, as needed by the problem, I can do this:

// go through each possible seat number, cutting off the very first and very last

// by use of offsets (1023 is the biggest seat ID), so get array from 0 ...1007

const nums = Array.from(Array(1023 - 16).keys());

// now just increase each number by 8 so we go from 8 .. 1015

const possibleSeats = nums.map(n => n + 8);

Fun stuff!

[Adam Morris]

Hmmm is your google form using a date input, as pictured? If not, why not?

Validating dates in JavaScript is a heavy topic. There are libraries like moment.js that do a great job of that, and are useful for so many other reasons. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5fca7773.1c69fb81.3cab.73de%40mx.google.com.

[CBM Services]

Yes, google form is using a date input. Browser allows clicking on calendar to choose a date or you can type the date in. Unfortunately when people type the date in, some are typing 20 for the year. Google form accepts that. Form submission is entered into a spreadsheet and spreadsheet displays it normally as if all is fine. But the date methods choke on it which caused my problem.

I will check into your suggestion and look up that library.

Thanks,
George

---

From: Adam Morris
Sent: ‎2020-‎12-‎04 11:09 PM

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHQVb%2BSviFTZPpLQaX3BtawSuegp0cb1bapav7Sz93KqEeyzpQ%40mail.gmail.com.

[Jean-Luc Vanhulst]

It's so fun to work through them, brush up on specific language items you don't use much - and see other people's approaches.

I ended up using   var a =Array.from(c);, which does NOT do that weird reordering of the letters!

For finding the 'seat', since I already start with an array with the input (and I leave the seat ID in there) - I can simply sort the array and find the 'missing seat' where the gap is?

Anyway - I did run into THIS -  Is that a Bug or a Feature? :) 

my array has NUMBERS (as seen in the debugger window) - but after SORT it looks like these 'numbers' were sorted like they are strings? 

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHQVb%2BTgVRueG8ZjJpMSYOtKB88eczThrQJZr-Gnvt2agG07Vg%40mail.gmail.com.

[Adam Morris]

Yeah sorting in javascript by default converts to string. 

// sorting by default is alphabetical, so we need a callback that

// does not convert to a string by default

seats.sort((a, b) => a - b);

Same as 

seats.sort(function (a, b) {

return a - b;

});

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ50DKePBFhRcGH_eX74rde%3D8O%3DzUw-66p7h7aF7GW9k6%3Dg%40mail.gmail.com.

--

http://classroomtechtools.com

[Jean-Luc Vanhulst]

That’s how I updated it. It was surprising and a bit scary default result for a numbers array :). Thanks for confirming it’s a ‘feature’ :)

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHQVb%2BSZkvpjEeE%2BviA1_EyL6qJZQPyj%3DYm5R-PFDxMvuk_vZw%40mail.gmail.com.

--

[Adam Morris]

Day 6 was easy conceptually, and so it was a great practice at using functional methods to derive answers. For the second part especially, I used a nested Array.reduce function with an additional count for Array.every.

A language feature that I use often is this:

// save the shortest (first one)

// and all remaining arrays (from index 1 to end)

const [shortest, remaining] = [answers[0], answers.slice(1)];

It's the same as doing

const shortest = answers[0];

const remaining = answers.slice(1);

But doing that required me to have them sorted by length, and so used yesterday's sorting method again.

https://script.google.com/home/projects/1nbIXikKL7P6Ibayam8JrTA8UPaudcyuYzOwe_mEPLEwvVHwhiKeDIlEN/edit