Newb to Javascript, learning to populate a spreadsheet...

[Burt Paulie]

Greetings to the GAS community! I love your work and dream of the day when the Home Assistant can access a GAS-like ecosystem (far in the future).

I am new to coding, but handle editors well enough. Where I do not suffer is in having creative ideas I would love to code. Currently I am engaged with the Fireship community about the Angular platform with interests in developing medical information systems for low-cost clinics around the world. I also have interests in game design.

GDrive is incredible to me which is of course why I am here in the GAS forum. To help myself learn to code, I am trying different things in different places, but immediately I would like to learn how to populate a spreadsheet with some simple prime number data: 3 columns {O=natural increment excluding zero, P=prime number, Z=base 26 increment in english characters} by 84 rows in each block.

I have found code examples for populating prime numbers on StackOverflow here and I understand the demand for a doGet function, but I could use some tutoring about how to learn what the final form should feel like. I do not want others to just hand me an answer. Does anyone have any suggestions about how to learn more about GAS applications? I have made some progress learning to expand the function of G Suite Solutions found in the gallery, but I have little experience building a piece of code from the ground up.

[Alan Wells]

A sidebar or dialog box can be displayed in a Sheet, allowing the user to enter settings,

then click a button to get the code running.

If all you need, is to get the code running, and no user input, then you could use a custom menu in the Sheet.

You can add an image that looks like a button in a Sheet, and assign a function to it.

Or do you want the starting point to be a website that puts the answers into any Sheet?

There are lots of other possibilities.

What's the starting point?

What's the flow of events?

Is this for personal use?  public use?  5 users?  1,000 users?

Will there be lots of concurrent users?

Do you need it to scale to unlimited users all using the same database?

Can it be unlimited users, but each one has their own spreadsheet?

On Sunday, June 28, 2020 at 12:36:38 PM UTC-4, Burt Paulie wrote:

Greetings to the GAS community! I love your work and dream of the day when the Home Assistant can access a GAS-like ecosystem (far in the future).

I am new to coding, but handle editors well enough. Where I do not suffer is in having creative ideas I would love to code. Currently I am engaged with the Fireship community about the Angular platform with interests in developing medical information systems for low-cost clinics around the world. I also have interests in game design.

GDrive is incredible to me which is of course why I am here in the GAS forum. To help myself learn to code, I am trying different things in different places, but immediately I would like to learn how to populate a spreadsheet with some simple prime number data: 3 columns {O=natural increment excluding zero, P=prime number, Z=base 26 increment in english characters} by 84 rows in each block. 

I have found code examples for populating prime numbers on github and I understand the demand for a doGet function, but I could use some tutoring about how to learn what the final form should like. I do not want others to just hand me an answer. Does anyone have any suggestions about how to learn more about GAS applications? I have made some progress learning to expand the function of G Suite Solutions found in the gallery, but I have little experience building a piece of code from the ground up.

[Burt Paulie]

For me the project is purely personal, and by that I mean calendrical. I'd like the script I write to populate my sheet in the manner I described, sequential prime numbers in 8 blocks of 3 columns with 84 rows per column. And like I said, I'm a newb, so knowing which library of Javascript functions to research is definitely the ball at the moment. 

So let's go with that. What functions do I need to review to understand how to build this kind of code? With respect to the style, it does not have to be fancy. For limit's sake, let's say primes less than 10**6. I know what cells the primes will go in, but the iteration will not be smooth as in a population down a single column. I have no demand for variability to my result, so all that GAS has to do is populate the sheet once and that's it. I suppose this is mostly about my first practical use for loops of my own design.

For the Code.gs file I have some basic suggestions from StackOverflow about a method to populate primes. I think a doGet is involved somewhere, but my knowledge of where and how exactly is wanting. I can look up the doGet in the docs but I might need help with the implementation in relation to the population of the primes in the sheet. Controlling the flow of the loop across the sheet is a bit of a mystery at the moment but my instincts say that it is done in the .html file with some kind of modification to the placement of the variables after the increment occurs in the loop. Once the loop hits 84 entries, it jumps to a new column. And there will be three loops demanding population: the basic increment (O), the prime increment (P), and the base 26 increment (Z). Also, I would like a two row header populated over every 3 column block with Week N in the top 3 merged cells and O(ordinal), P(prime), and Z(base 26 using the English alphabet) below that. The color scheme is gray for the headers and three shades of ROYGBIV over every 4 rows (3*4*7=84).

I can't think of where to go deeper at the moment, but the original question still stands: What functions should I review to help build this? Sir Thanks-a-lot...

[Alan Wells]

doGet() is a reserved function name.  It's reserved to "listen" for an "event."  The event is a GET request, made to the Apps Script project.  It's named "get" because it's a request that is typically used to get HTML and display it in your browser.  You don't need the doGet() function to do what you want to do.  You can trigger your function from a custom menu.  To get to the Apps Script documentation, you can click the "help" menu from the code editor, and choose "documentation."

You'll need to review the math functions for JavaScript.

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

https://www.w3schools.com/jsref/jsref_obj_math.asp

To set values in the Sheet, you'll need to first get a range.

So, review the "getRange()" method.

https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#getrangerow,-column,-numrows,-numcolumns

Then you'll need to setValues()

The values need to be in a 2 Dimensional array.

So, you'll need to know how to create an array.

https://www.w3schools.com/js/js_arrays.asp

[Burt Paulie]

I've got a few of the basic variables pasted into my Code.gs file and commented out until needed. I also have two functions I found on Stackoverflow for generating primes but I don't even really know how to make them log to the console, let alone populate a column. 

Learning To Print Primes is my project file. My goal is to recreate and expand on the Print Primes Template sheet using GAS. My target is the Print Primes Canvas in the same file. For whatever reason, my present learning fixation is on automating the getRangeList method so that I don't have to enter the ranges manually. My column classes are O, P, and Z. I'll start with O. It is nothing more than a basic incremental sequence starting with 1 and counting to whatever limit is set for the size of the file (around 10**6). The first range for this progression is A3:A86. It then picks up again at D3:D86 happening every 3rd column from the first (A, D, G, J, etc.) through column V. The sequence should continue from A87:A170 and progress iteratively in similar fashion to the first row of blocks.

How do you automate this? I feel like I should know somehow, but then again I've never done this in my life.

After handling the O columns P and Z need addressing. P is for the primes and Z is for base 26 in English letters.

Am I starting in the right place?

[Burt Paulie]

Can't log to the console because the code is running in the cloud and not on the browser. Got it. Learning logger...

[Burt Paulie]

Okay, so I can see that the code I got from Stackoverflow does what it says as is in the Logs. And console.log(arr); works fine (even better than Logger, which returns primes with floating point). I know that with the two functions I have at present, I can change the display value for whatever limit I would like. Those two functions control the input to the P columns found at B, E, H, K, N, Q, T, & W. If I want to fill those columns in my sheet, is that where the setValue() method comes into play?

This is what I have for my O columns so far, but I don't yet know how to format the output for printing in the RangeList of the sheet:

function isOrdinal(num_o) {

    for ( var i =1; i < num_o; i++ ) {

[Alan Wells]

Don't use:

setValue() - Sets value in one cell

Use:

setValues() - sets values in multiple cells

"s" on the end.  Plural.

The data must be in a 2D array.

Each inner array represents one row.

Each value in the inner arrays represent one cell.

[ ["One Cell Content"] ,  ["One Cell Content"]  ]

The above array would put values into 2 rows, and only one column.

[Burt Paulie]

Is what I have wrong? It is what my mind keeps intuiting: //var rangeList = sheet.//var rangeList = sheet.getRangeList([['A3:A86'], ['B3:B86'], ['C3:C86']]);

//rangeList.setValues([[num_o, num_p, num_z]]);

Also, I'm confused about how to iterate this, but I have the Array Iteration docs, so I'll keep at it...

[Adam Morris]

Hi Burt, thanks for using the community. 

I think it's great that you're not looking for someone to provide a solution, instead you're looking to leverage the community to help you learn how to learn. :)

So from what I can see you have a list of prime numbers, and you'd like to understand how to populate a sheet over three columns?

There are a few layers to understand here, and one is that there is a JavaScript layer in which nearly everything you can do in JavaScript, you can do in AppsScripts. (There are limitations but these don't concern us here.)

So if you need to understand how to generate a list of primes, you can use regular JavaScript for that. You might see in various forums and the like that there are different versions of JavaScripts; we can use up to "ES2018" … these different versions of JavaScript might throw you off a bit, but at least you know there has been some advances in the language itself.

Another layer is that you have to use some of Google's spreadsheets APIs in order to write to the spreadsheet. There are many methods to write, but by far the easiest, most accessible is the one you get with .setValues(). Documentation on that method is here: https://developers.google.com/apps-script/reference/spreadsheet/range

But how do you use it? Do you just do this maybe?

setValues()

No, because Google defines that method on a range. So it's expecting you to basically, build up to it.

So how do we get a range? Our starting point for all these things is SpreadsheetApp, and we can build from there. Here's the documentation for that: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

const sheet = SpreadsheetApp.getActiveSheet();

That returns a Spreadsheet object. That's no help, right, because we need a range object. But it has methods which help us get the range object:

const range = sheet.getRange('A2:C18');

Ahah! So now we can do this:

range.setValues( values );

But that means we need to set up that values variable. And for that, we need a muti-dimentional array, and we're back in the JavaScript layer, trying to figure out how to do make a multi-dimentional array (an array of arrays).

So if you have an array and need to divide that up into other arrays, chances are this is a problem programmers have come across before, and it is known as "chunking." 

Another tip I'll give is if you found using the Logger cumbersome or slow, I like to use this library, which I've found makes things much quicker, and in fact wrote it when I was kinda annoyed at how slow it was.

https://github.com/classroomtechtools/modularLibrariesV8/blob/master/Logger/Logger.js

The code itself is complicated, but you don't need to understand it. Just paste the raw code into a script file (call it anything you want), and anytime you use Logger.log() statements it'll write to the spreadsheet instead of having to go through the slow logger. The logger will also report to you which spreadsheet it created and you can find it that way.

Hope that all helps!

Adam

--
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/97ed5087-7b1f-4472-82e1-93964871b805o%40googlegroups.com.

[Burt Paulie]

Here's my attempt at correcting the syntax, but I still find my erudition dubious. I was missing brackets I think...

//var rangeList = sheet.getRangeList([['A3:A86'], ['B3:B86'], ['C3:C86']]);

//rangeList.setValues([[num_o], [num_p], [num_z]]);

[Adam Morris]

Hi Burt,

So getRangeList is expecting an array of strings, so in JavaScript that is done using this notation:

const a1Notations = ['string', 'string'];

Each string can be an a1notation, like "A1:D4".

This method is usually used when you're looking to get ranges in non-adjacent column or something, but you could continue working in this way.

Then you can iterate over the array that is returned, with each item being a range object, which has setValues method.

--

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/ac8cd08f-14e5-413e-b4e6-88ef100e6c9bo%40googlegroups.com.

[Adam Morris]

Real-time followup:

That last paragraph I wrote is actually incorrect. The result of getRangeList is a RangeLIst object, and you can iterate over them, but only after calling .getRanges(), which will give you range objects.

[Burt Paulie]

What's the difference between using the const keyword and var? I don't know everything I need just yet, but I have these variables at the top of my page. Does the rangeList and setValues look formatted correctly. Alan at AJ_addons has been offering me pointers. And I have another question...

...about the rangeList, I want the list to repopulate itself repeatedly two rows under the last entry. So the program should repeat the format again starting at A89:C172 and then A175:C258, etc.until it reaches its upper limit, in this case less than 10**6 rows of 3 columns each. How do I do that? And as I'm asking I still have yet to review the Array Iteration Docs...

//var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

//var sheet = ss.getSheets()[0];

//var rangeList = sheet.getRangeList(['A3:C86', 'D3:F86', 'G3:I86', 'J3:L86', 'M3:O86', 'P3:R86', 'S3:U86', 'V3:X86']);

//rangeList.setValues([[num_o], [num_p], [num_z]]);

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Adam Morris]

Both const and var declare variables, they just have a different scope. You can replace const with var in the beginning of your journey; var is more flexible for someone starting out. Sorry to give you that extra bit of confusion there, but const has become automatic to me.

I would suggest that you first attack this problem by generating the prime numbers into an array. Also, practice first by only manually inputting the first 10 prime numbers and see if you can get it them into one column, then two. Then you can start abstracting away and seeing how to get multiple columns, below them as well.

Don’t try and complicate things too far ahead of yourself.

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/97ed5087-7b1f-4472-82e1-93964871b805o%40googlegroups.com.

--

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/4a5006e8-b224-4966-aeed-22646f8d817ao%40googlegroups.com.

--

http://classroomtechtools.com

[Burt Paulie]

I'm going to be working on this project today and I thought I would shoot something out about yesterday. I found the Google Sheets API v4 docs which helped address the write function I'm trying to foist as well as the spreadsheets.values.updates method docs, but I am still entirely unable to put the pieces together. I'm posting what little pieces of code I slopped together here, but the page might as well be blank as far as I can tell. I'm a good teacher, or perhaps I should say I have the ability to present a clear and rigorous pedagogic approach if I feel so inclined, and I'm noticing it's the connection of each piece of code to the others that seems most difficult. The documentation says you need x, y, and z but it doesn't frame them in a context relative to the other pieces of code you need. I feel like I'm missing an important architectural perspective that only really comes with experience.

Philosophizing aside, I am definitely taking your suggestion to try and do less. My new goal is to understand how to print my ordinals in column A from A3:A86. That's it. It seems like it should be very simple, but I still don't really understand it. The Google Sheets API v4 has two docs which seem directly relevant: Reading and Writing Cell Values as well as Method:spreadsheets.values.update. I can read the requirements, but as I said I'm missing the architectural structure of how the components relate to each other. And again, my only goal at this point is to print ordinals in a column. That's it.

Can you give me a kind of step by step architecting procedure? How is the code for just printing the ordinals in my spreadsheet column built from step/line one to the final functionality?

PS: This is the sum total of my crap attempt so far:

/*var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var range = sheet.getRange('A3:A86');

getSheetId

valueInputOption*/

function isOrdinal(num_o) {

    for ( var i =1; i < 85; i++ ) {

        return (i);

        }

    }

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/97ed5087-7b1f-4472-82e1-93964871b805o%40googlegroups.com.

--
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-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4a5006e8-b224-4966-aeed-22646f8d817ao%40googlegroups.com.

--

http://classroomtechtools.com

[Adam Morris]

Hi again Burt,

You are right about the documentation. It is intended for programmers who already have developed some of the connective tissue we need to understand them. 

So to me the part you're missing is that you have to build an array in JavaScript, and this array has to be two-dimensional, and that is what you'll send to the google APIs. For example, this is a one-dimensional array of primes:

var array = [2, 3, 5, 7, 11];

If I wanted to put those numbers into a row, it'd have to look like this:

var row = [ [2, 3, 5, 7, 11] ]; 

What about if a column?

var col = [ [2], [3], [5], [7], [11] ];

Those are an array which contain arrays — multi-dimensional array.

The above is JavaScript arrays, with the data shaped into what the Google APIs are expecting to receive. You can use JavaScript's Array.push method to populate arrays in a for loop.

function output () {
  var boundary = 15;
  var array = [ ];
  var row = [ ];
  for (var num=0; num < boundary; num++) {
      row.push(num+1);
  }
  array.push(row);

  ...

}

That gets you the array for a row. Let's use Google's API to get a range, and then use setValues to write to it. Here I'm using getRange(rows, columns, numRows, numCols) format:

function output() {

  ... above

  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(2, 1, 1, boundary)
  range.setValues(array);

}

So, how would you change this code to make it output a column, rather than a row as it does now?

Happy programming!

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/97ed5087-7b1f-4472-82e1-93964871b805o%40googlegroups.com.

--
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/4a5006e8-b224-4966-aeed-22646f8d817ao%40googlegroups.com.

--

http://classroomtechtools.com

--

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/a58abf4b-1d58-4385-9885-c48fefc7c624o%40googlegroups.com.

[Burt Paulie]

Thanks for getting into this for me. I'm looking at it now, but I'm heading out for an errand. I loaded up your Logger.js file. Thanks for that. I'll see it work once I've got something written. I never feel able to learn much from the tutorials because there is rarely a goal-oriented context that creates small-sized code samples that actually achieve a personally meaningful goal. Cheers!

Sir Thanks-a-lot,

Burt Paulie

925-548-7077

burtran...@gmail.com

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

[Burt Paulie]

Okay, so...

I've begun working with your suggestions without working at the column formatting issue yet. There are a couple of issues I don't have an answer for now:

When I try to run the function as is I get a discrepancy between the number of rows in the data (0) and the number of rows in the range (1). The discrepancy is in line 20 which I've highlighted:

function isOrdinal(num_o) {

  var boundary = 8;

  var array = [];

  var row = [];

  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('print_primes_canvas').getRange(3, 1, 1, boundary)

    for (var i = 1; i < boundary; i++) {

      row.push(i + 1);

    }

      range.setValues(array);

      array.push(row);     

}

I lack the experience to know what I'm missing there.

Other questions: Is the expression formatted correctly, or should range.setValues(array); be above the for loop?

Also, In the parameters of the function isOrdinal I have (num_o). Where will that designation (num_o) become relevant? In other words, what's it for?

Lastly, in the row.push(i + 1) method, what is the +1 for? Does it prepare the array for the next increment?

I got the Logger formatted, but I won't get to see it work till I get the basic function correct. As for changing the input progression from row to column, I understand the difference between the two formats, but I'm unsure what I have to define to make it work. I haven't looked at it long and I'm taking a break. Thanks for being there man. Sir Thanks-a-lot... 

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/97ed5087-7b1f-4472-82e1-93964871b805o%40googlegroups.com.

--
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-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4a5006e8-b224-4966-aeed-22646f8d817ao%40googlegroups.com.

--

http://classroomtechtools.com

--
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-community+unsub...@googlegroups.com.

[Michael O'Shaughnessy]

Just looking at the last code snippet you shared....

First, yes, the array.push has to come before the range.setValues statements

Second, the error is coming from a range you set with 8 columns is trying to be filled with an array that only has 7 columns of data.  This is because your loop counter is set to be < boundary.  This means the loop will go from 1 to 7 then STOP at 8, meaning it will only make 7 loops.  If you change it to <= boundary you will then get 8 full loops and the code will work.

Third, the row.push(i+1) is just pushing data to a new column in the "row" array.  You could easily change the "i+1" to anything... like "i * 5" or even the string "dog" (which will get you 8 columns of the word "dog").

Hope this helps!

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/97ed5087-7b1f-4472-82e1-93964871b805o%40googlegroups.com.

--
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/4a5006e8-b224-4966-aeed-22646f8d817ao%40googlegroups.com.

--

http://classroomtechtools.com

--
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/a58abf4b-1d58-4385-9885-c48fefc7c624o%40googlegroups.com.

--

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/c7d911e9-1e01-4457-9b93-173c473bb7ddo%40googlegroups.com.

[Burt Paulie]

You are a champ! In the words of Celine Dion: "How can I live without you?" I'd probably survive but I would feel far less fulfilled...

Thank you,

Burt Paulie

925-548-7077

burtran...@gmail.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLjf%2BAHrrzBH4%3DA7V%2BG%2BW%2B5UShOdi_yzrENxG2p7Z76EuA%40mail.gmail.com.

[Michael O'Shaughnessy]

My pleasure Burt!  We were all beginners at some point in time... and I too have fallen victim to the same thing you ran into!!!

Just an FYI and for future reference... Spreadsheet columns are "1" based where arrays are "0" based.  What this means is that the first column in the spreadsheet is "1", the second is "2" etc.  For an array, the first element is at an index of "0" and the second is at index of "1".  This will trip you up when you start looping through arrays and forget that you need to start with "0" for the first element!  For example, lets say you have an array called "theData".  You get the first element by writing "theData[0]" and all arrays have a "length" property.  Now, let's say you want to loop through the array your FOR loop would look something like this:

FOR(var i = 0; i < theData.length; i++){

  var item = theData[i];

//and do some more...

}

If there are 3 items in the array then the length will be 3 BUT, the indices will be 0, 1,  and 2.  So if the loop goes until i = the length of the array.  If I get errors here, the loop condition is usually the first thing I check.

Also, as you work more with spreadsheet data be mindful when you get the range of data... are you including the header row?  If so, make sure your loop condition is taking that into consideration!

Last but not least, you can avoid many FOR LOOP issues by using the various array methods that are available... forEach, some, every, map, reduce, sort and filter (to name a few)....  It took me some time to start taking advantage of these but my coding has dramatically improved as I have started using them.

Happy coding!!

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

[Burt Paulie]

I have a question simple to put, but not so simple to answer: How are we teaching about issues of order in operations relative to the performance of a language and its interpreter? Whenever I go to the docs there is always some snippet that gets you from A to B, but when I go to something like the GAS Solutions page, I get a complete program which admittedly has instructions and comments, but little natural language telling me why A precedes B and C follows B, etc.

My question seems rhetorical for me in all honesty, because I often feel I am looking at a bigger gap in pedagogy than some people might like to admit to. There are so many variations on what code can do that each case is taken on a case by case basis. However, if I use my imagination about the first GAS solution I ever modified, I seem to be constantly saying to myself that a deeper, more articulate commentary would be very worthwhile. I personally want description of the purpose, scope, and dependencies of every variable, constant, and function that goes into the program. I want the documentation of why a thing happens right next to what is making it happen. So much of good coding relies not only on meticulous detail, but on the presentation and formatting of accurate information. One of my biggest experiences with web/software dev is my gripes over bad designs. The buttons and links will always be necessary, but some solid descriptions of an entire program in excess of a few lines or commands seems like something I am missing. GAS is very thorough and required intense acts of assembly, back-end, and front-end programming. And sometimes you get a bigger piece of code in a box. But when do we get smaller sided programs with intricate verbage about the hows and whys of their function. 

I guess my word of the day should be pedagogy. The GSuite Solutions gallery has some great work, and I can certainly construct my owns thoughts, facts, and opinions about the products that have been produced with GAS without telling a soul. But what about reverse engineering several useful programs in a blog...with greater line by freakin' line of natural language details.

That's what I want: greater natural language code details about single program architectures onweb! Like a dissertation on Angular Firebase...

[Burt Paulie]

Big thanks to aj, Adam Morris and Mike O. for their willingness to work with me so far. The killing blow came when after I rambled today about having done nothing but wanting something, I changed one line with one character (an inequality) and Adam's Logger printed to my spreadsheet. I couldn't have done it without you! I'm going stick with it until I see something more substantial. I have the template, now all that remains is to reverse engineer it. Hey Google, rewind...

[Adam Morris]

Great stuff everyone!

I agree we need more learning resources. He’s one I came across from the community:  

https://courses.benlcollins.com/

On Fri, Jul 10, 2020 at 10:38 AM Burt Paulie <burtran...@gmail.com> wrote:

Big thanks to aj, Adam Morris and Mike O. for their willingness to work with me so far. The killing blow came when after I rambled today about having done nothing but wanting something, I changed one line with one character (an equals sign) and Adam's Logger printed to my spreadsheet. I couldn't have done it without you! I'm going stick with it until I see something more substantial. I have the template, now all that remains is to reverse engineer it. Hey Google, rewind...

--

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/e357adc2-77a6-4c0a-be70-c12df713846bo%40googlegroups.com.

--

http://classroomtechtools.com

[Alan Wells]

Regardless of the programming language, developers want great tools, documentation and learning resources.

As is with everything in life, there is a cost and a benefit to everything.

No matter what the need is, there needs to be incentive to provide a solution to the need.

The incentives are basically either goodwill or money.

Goodwill - Somebody wants help you for free.

Money - Somebody wants to get paid to help you.

I'm in favor of both.  Google tries to encourage both.

We are a long way from the utopian world where perfect Apps Script tools and resources exist.

But what are you going to do?

Something is better than nothing.  Take what you can get, and keep moving forward.

I highly encourage bitterly complaining about issues,

because sometimes that's the only way that a problem gets fixed.

For some reason that's the way the human brain is programmed. 

(Who are you going to blame? Who wired the human brain?  That's a different subject.)

But, I'm also in favor of being part of the solution.

No one person can provide all the resources needed.

Some people provide answers, some people provide code, some people provide books,

some people provide tutorials.

So, there is lots of individual effort, but very little cooperative effort.

That's a big problem, because there are some needs that are bigger than an individual can fulfill.

What I'm saying is, WE could provide better stuff by working together.

But getting people to work together is difficult.

If you can discover the secret to getting people to cooperate for the greater good,

then please contact me immediately.  Haha.

On Friday, July 10, 2020 at 6:14:17 AM UTC-4, Adam Morris wrote:

Great stuff everyone!

I agree we need more learning resources. He’s one I came across from the community:  

https://courses.benlcollins.com/

[Kim Nilsson]

I would call communities like this a cooperative effort, as often more than one and/or different people help out or discuss things to the best of their abilities. This thread is a good example of that.

[Efficient Small Business]

Hey Burt,

This may be a helpful resource for you:

https://www.youtube.com/channel/UCg2CWqNToa_QjSG52zCJuCg/playlists

[Burt Paulie]

I type all the time (like any avid coder) and I watch myself err over and over again. Like so many of us, I realize that false positives are a necessary part of language and of learning. I had to ask the other day why my error was giving me GAS. Oops! I mean why GAS was giving me an error. It told me that the number of columns in my data was not equal to the number of columns in the range. mro.michael gave me an answer that made the function, but I still didn't know why I couldn't find it myself. Sitting down just now, I realized I had been looking for numbers and not the name of the variable. 7, 7, 7, where's the other seven? Boundary, boundary, that's not it, where's the bloody 7? Oh yeah, that's what boundary means! Thanks guys...

For lack of experience, I couldn't see it.  

[Burt Paulie]

I thought I would post an email I sent to my stepfather after I finally got Adam's logger to work. He's a lifelong coder that has only ever programmed in FORTRAN. Mostly he was a vice presidential project manager working in the field of civil engineering as a transportation demand modeler. He recently retired but went right back to work for a one woman show. I never learned a thing about computers from him while I was young, but this was intentional. Personal details aside, I find it very funny that in seeking to learn to code, the biggest, most powerful step I can take is to rely on someone else's code. I wanted to log my functions to a spreadsheet. I was willing to study the docs and learn what is required to push data to the sheets. So your very own Adam Morriss shows up and says, "Oh, I wrote a program for that on Github. Slap it in and watch it fly." 163 lines of code I have virtually zero expertise in producing at this point just to print to a sheet. Now that I say it, my own words about a kind of gap in pedagogy are ringing true with me. I remember looking at the docs going, "Which one of these functions goes with which other function to make what happen?" I get it a little more. I look at the docs. 5 to 10 lines of code for something there is no example for in many cases. What I mean is, I don't see it in the context of a cause and an effect. I have little code to pair it with, and I can't write it in and see what it can do for me. And that is just those "5 to 10" lines of code. Enter Adam Morris: 163 lines of code. Tell me if I'm wrong about a gap in the pedagogy. So many code schools and pages. So many miniscule descriptions. Fewer step by step architectures that post detailed documentation right next to the code telling the student why each step and each element does what it does. So I'm asking. Formally. I'm I right about a gap in the pedagogy?

On Friday, July 10, 2020 at 5:34:59 AM UTC-7, aj.addons wrote:

[Burt Paulie]

Here is that email...

Hilarious Coding Gap Gaffes...

Burt Paulie <burtran...@gmail.com>	Jul 9, 2020, 7:57 PM (3 days ago)
to William

It's so funny how powerful programs are the successive work of generations of trial and error in minor advances of functionality. I'm studying GAS and have found three coders to help me, but to do what I want to do, I literally needed some expert named Adam Morris who had programmed something called Logger available on Github for free. I could never program a thing like that on my own at this moment in time. So in order to learn the basics of Javascript and HTML for Google the underpinning of my success was program I can't really understand the architecture of at present.

I my own defense, within a day I had crapped out a non-functional blob of cut and paste that should get me about half way. The good news is that when I stepped back and composed an original function I was only one character away from making Adam's Logger actually work.

Do you know what Google calls the program written for the GAS ecosystem? Logger. So Google programs Logger to test code and embeds it in GAS. Then when ask how to make something happen, a guy comes on and says "Oh, by the way, the GAS Logger is really slow. Use the code I wrote and posted on Github to make your efforts successful.

For me the lesson is in fact that I get nowhere without dozens of programs written by dozens of people who set up to show me that they are just waiting in the wings to give you the tools they developed to make your functions run.

If Adam wrote a Logger that prints to my spreadsheet in less than 200 lines, why wouldn't Google include that code in their interface? Because that code came from a person who wants people to see what his work is capable of helping them do. 

It feels like a very common entry-level scenario that was scripted on purpose. The docs say that the programmers running the forum don't work for Google, but I don't really believe that. It's just another sign post for well crafted distributional ethics. Whatever the case, It is uncanny how my conscious efforts left me only one character away from the most basic level of success logging to a spreadsheet. Much thanks for Google to being thorough and well prepared. 

I hope you are well. Have fun...

[Burt Paulie]

I have a function that prints to the sheet with the Sheet Logger. I have the description of how GAS handles arrays, rows, and columns. What I don't know how to do is get a function to assign multiple arrays with one item each to the larger array so that the numbers print down the column. I see the row.push method running for() in the row variable. I see the array.push method push the row variable to the array variable. And finally range.setValues(array) sends the values to the sheet.

Now how do you push sequential values to one array a piece nested within a larger array?

This is what is working now...I'm wondering how to implement a var col = [] with a for loop that prints multiple arrays. 

function isOrdinal(num_o) {

  var boundary = 7;

  var array = [];

  var row = [];

  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('print_primes_canvas').getRange(1, 1, 1, boundary)

    for (var i = 0; i < boundary; i++) {

      row.push(i + 1);

    }      

      array.push(row);

      range.setValues(array);     

}

[AD:AM]

Hi Burt - I will have to come back later to explain further, but have a look at this function:

function printOrdinals() {

  const rowBoundary = 7;

  const colBoundary = 3;

  const result = [];

  for (let i = 1; i <= rowBoundary; i++) {  // i = 1, 2, 3 ... 7

    let row = [];

    for (let j = 0; j < colBoundary; j++) { // j = 0, 1, 2

      row.push(i + j * rowBoundary);        // when 1 = 1, reult for row is 1, 8, 15

    }

    result.push(row);

  }

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('print_primes_canvas')

    .getRange(1, 1, rowBoundary, colBoundary)

    .setValues(result);

}

HTH

Adam

[Adam Morris]

Hi Burt,

Yes, agreed, but a bit more context is called for.

The appscripts ecosphere could use more tutorials, teaching materials, and the like, and other ecospheres have plenty. You could for example check out how to build websites with vanilla HTML/CSS/JavaScript and find a plethora of material. You won't be able to write to spreadsheets with that technology, but you can apply what you've learned there to appscripts later.

It's usually up to the community to provide such material as a kind of cottage industry. Much of the appscripts documentation that Google provides is actually intended for a programmer who already has some concepts and understandings under their belt.

As for my logger script and its 100+ lines. Indeed, what it does is output to a spreadsheet, which is what you were trying to do, but that is where the similarity ends. The extra lines of code are because it's doing extra, more complex operations ina addition to outputting to a spreadsheet. It redirects the output of Logger.log (which by default is to the built-in logger facility) to a spreadsheet. I wrote and shared that because the current logging facility is quite slow to show the results, whereas redirecting output to the spreadsheet is nearly instantaneous. It also does other things, like arrange the output into columns, draws a line with each new execution, and then puts all that into the spreadsheet.

That script uses programming concepts that a beginner would find overwhelming. So that also explains the gap. 

Regards,

Adam

https://classroomtechtools.com

--
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/8701cde6-dcea-4fe9-b962-8f35529f33efo%40googlegroups.com.

[Burt Paulie]

I'm migrating some of my inquiries to StackOverflow in an effort to spread out the love so to speak. I have to admit, if I were a teacher required to type all of my replies about something I already understand it would get tiresome having to detail the minutia of things I already explained. If anyone wants to follow my questions there, feel free. 

GoogleAppsScript/Javascript Syntax Question...I'd like some semantics regarding the Machine Behavior
https://stackoverflow.com/q/62878188/7222747?sem=2

[burtran...@gmail.com]

As one very curious mouse in the house, I have realized over my first steps with GAS that this is very much where I want to be. I want Google Home to have full access to my Drive. By my understanding, I have more leg work to do off the keyboard than on to achieve this worthy goal: I'd like to become a GDE on assembly and database programming for visual/voice interface with Google Home Assistant and Google Tables. Medical information technology is fascinating to me, and I love the idea of an entirely more facile and customized health care suite native to the Home ecosystem. Google Cloud is huge, but there are more threads to pull on visual/voice interface than will ever show easily online. I'm not memorizing, I'm interpolating constantly. Adam Morris offered me his logger to boost my first efforts. From a project management standpoint, it showed me how heavily I rely on other people's 100% to achieve a functional result. I'd actually need to hear some people talk about coding in person to understand what I'm facing as a non-employee looking to help willing Google employees mold their 20% time.

20% time is the future of new product development at Google. I'm ready and willing to make it my 80% time. I've got 1 to 2 years before I expect to hear of the FBI's investigation into my media accounts, maybe even 3. At ten to twenty years in the future, I hope for the death of "Coolidge" and "Roosevelt" as soon as possible, but I have very little power in this matter. When they are gone, it will be exciting to see a more enthusiastic core product push that takes Calendar out of the closet and fronts some niche Home services as standard functions of the Drive ecosystem. Until then, I don't mind bouncing around against the tide of "Coolidge and Roosevelt's" modern day Inquisition inside or outside the Google Dev Community and their friends. Frankly, I'd rather have more whole people than more project management apps. I'm not afraid of blame so much as I am of a potentially disappointing and short-lived product. No one needs a Gwynne Shotwell without a SpaceX.

I have to admit, Mail Merge surveys and recordkeeping are far less exhilarating than being with people actually programming something new. I want to be there with the rock stars while they make their code of the future  live and breathe. Truckin' like the Doodleman...