Advent of Code w/ AppsScripts

97 views
Skip to first unread message

Adam Morris

unread,
Dec 1, 2020, 6:03:07 AM12/1/20
to google-apps-sc...@googlegroups.com
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:


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

unread,
Dec 1, 2020, 6:54:04 AM12/1/20
to google-apps-sc...@googlegroups.com
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.


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

unread,
Dec 1, 2020, 6:54:46 AM12/1/20
to google-apps-sc...@googlegroups.com
oups 
Column C : formula = VLOOKUP(Bi;A:A;1;False)  


Emeric Hochart
Ingénieur S.I.


JETLANE SAS - AML - DECATHLON
Site du B'twin Village
4 rue du Professeur Langevin 
59000 - LILLE


Kim Nilsson

unread,
Dec 1, 2020, 8:34:28 AM12/1/20
to Google Apps Script Community
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

unread,
Dec 1, 2020, 9:59:45 AM12/1/20
to Google Apps Script Community
@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

unread,
Dec 2, 2020, 1:14:21 AM12/2/20
to google-apps-sc...@googlegroups.com
+1

Emeric Hochart
Ingénieur S.I.


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.

Adam Morris

unread,
Dec 2, 2020, 1:14:24 AM12/2/20
to google-apps-sc...@googlegroups.com
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.

Kim Nilsson

unread,
Dec 2, 2020, 3:52:56 AM12/2/20
to Google Apps Script Community
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 = 0x < num.lengthx++) {
      var a = num[x]
      for (var y = 0y < num.lengthy++) {
        var b = num[y]
        for (var z = 0z < num.lengthz++) {
        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 = [abc]
          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

unread,
Dec 2, 2020, 4:42:26 AM12/2/20
to google-apps-sc...@googlegroups.com
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.
--

Kim Nilsson

unread,
Dec 2, 2020, 5:14:54 AM12/2/20
to Google Apps Script Community
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

unread,
Dec 2, 2020, 3:08:33 PM12/2/20
to Google Apps Script Community
@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:Zwhich 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

unread,
Dec 2, 2020, 3:10:15 PM12/2/20
to Google Apps Script Community
Ah, it dropped the image.

2020-12-02 210940-Advent_of_Code-Day2.png

Adam Morris

unread,
Dec 3, 2020, 2:18:38 AM12/3/20
to google-apps-sc...@googlegroups.com
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.

2020-12-02 210940-Advent_of_Code-Day2.png

--
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.

Kim Nilsson

unread,
Dec 3, 2020, 2:29:29 AM12/3/20
to Google Apps Script Community
Yeah, I saw my son getting slapped with indentation errors all the time, as he's using python. :-)

Kim

Emeric HOCHART

unread,
Dec 3, 2020, 8:22:44 AM12/3/20
to google-apps-sc...@googlegroups.com
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.


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.

Kim Nilsson

unread,
Dec 3, 2020, 8:39:35 AM12/3/20
to Google Apps Script Community
That was very nice and short.

Kim

Emeric HOCHART

unread,
Dec 3, 2020, 9:02:12 AM12/3/20
to google-apps-sc...@googlegroups.com
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.


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.

Kim Nilsson

unread,
Dec 3, 2020, 9:08:26 AM12/3/20
to Google Apps Script Community
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

unread,
Dec 3, 2020, 9:10:08 AM12/3/20
to google-apps-sc...@googlegroups.com
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.


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.

Emeric HOCHART

unread,
Dec 3, 2020, 9:22:38 AM12/3/20
to google-apps-sc...@googlegroups.com
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);





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.


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.

Kim Nilsson

unread,
Dec 3, 2020, 11:15:51 AM12/3/20
to Google Apps Script Community
Thanks! 

Jean-Luc Vanhulst

unread,
Dec 3, 2020, 2:32:13 PM12/3/20
to google-apps-sc...@googlegroups.com
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 ] } );


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.

Adam Morris

unread,
Dec 4, 2020, 7:22:55 AM12/4/20
to google-apps-sc...@googlegroups.com
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';


CBM Services

unread,
Dec 4, 2020, 12:52:56 PM12/4/20
to google-apps-sc...@googlegroups.com
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

Adam Morris

unread,
Dec 5, 2020, 2:02:55 AM12/5/20
to google-apps-sc...@googlegroups.com
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

unread,
Dec 5, 2020, 2:09:26 AM12/5/20
to google-apps-sc...@googlegroups.com
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. 

image.png

CBM Services

unread,
Dec 5, 2020, 11:20:34 AM12/5/20
to google-apps-sc...@googlegroups.com
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

Jean-Luc Vanhulst

unread,
Dec 5, 2020, 11:55:59 AM12/5/20
to google-apps-sc...@googlegroups.com
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? 
image.png

Adam Morris

unread,
Dec 5, 2020, 7:12:46 PM12/5/20
to google-apps-sc...@googlegroups.com
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;
});

--

Jean-Luc Vanhulst

unread,
Dec 5, 2020, 7:48:43 PM12/5/20
to google-apps-sc...@googlegroups.com
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’ :)


--

Adam Morris

unread,
Dec 6, 2020, 1:07:33 AM12/6/20
to google-apps-sc...@googlegroups.com
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.


Reply all
Reply to author
Forward
0 new messages