The Goblins Fan Forum


https://www.goblinsforum.com/

Spreadsheet Scripting Tutorials

https://www.goblinsforum.com/viewtopic.php?t=1817

Page 1 of 1

Spreadsheet Scripting Tutorials

Posted: Sun Mar 11, 2018 4:56 pm
by Inspired
This thread exists to help the GMs to utilize Google Spreadsheets' functions to a bigger extent, making game updating an easier process.

Why Spreadsheets?
First of all, this is not a product placement. The reason I chose Spreadsheets is because the usage of it is free (as of the moment), stores data reliably, and it's really easy to share content through it. People familiar with other cell-based office suites (MS Excel, OpenOffice Calc, et cetera) may also easily translate their knowledge from those to this one and vice versa. Also, many GMs are already using spreadsheets to store player data, so having an additional tool in their hand just seems to be a good thing. If you have another favourite product and know how to write functions in the related language, feel free to transcribe the tutorials here into that one!

What are these scripts you've been talking about in the title?
Scripts are basically small programs that do stuff for you. Well, not exactly full-fledged big things like cooking your dinner, cleaning your shoes or taking the kids home... More like the functions you are probably already familiar with in spreadsheets' terms like summing values or formatting data. we will be talking about the latter, mostly.

Okay, how do I make these scripts?
To start making scripts for your spreadsheets, open up a spreadsheets page, preferably one you can edit. You can start an entirely new one or you can add scripts to an already existing one. If you are a GM, chances are you already have some lying around. Be warned though that scripts can make your page load slowly, or maybe breaking it if written wrong. If you want to be cautious I suggest you to copy your data you want to experiment on to a new document, experiment on that one, and copy back the scripts you are confident won't mess up your data.

To create scripts, Click on the >Tools< menu, and select >Script Editor...<. You should end up with a blank page where you can write scripts.

What am I gonna do with these scripts I write?
The primary aim of these tutorials is to help translating data from your spreadsheets to the forums, using the phpBB boards format. If you are a GM actively doing games or an avid forum user, you might have already noticed that to format your posts correctly takes practice and time, and sometimes also makes your raw text quite hard to read, making you bounce between text view and preview back and forth. And this is where these scripts aim to help you out.

Let's get started!

Re: Spreadsheet Scripting Tutorials

Posted: Sun Mar 11, 2018 6:09 pm
by Inspired
Tutorial 1
Script language, basic data extraction and usage - Part 1

Google Spreadsheets use JavaScript for their scripts. It is not the aim of the tutorials to teach you the language as there are many a great sources for that, so if you find the time feel free to learn it by yourself. Nevertheless, the basics will be explained along the way and it's a pretty straightforward and easy language to learn at a beginner's level, so don't be afraid! Thus, there won't be lengthy explanations of things that are not crucial, and I try to keep tangential information as minimal as possible. Let's focus on the goals we want to reach!

Functions
Our primary aim is to convert table based data into formatted text. This - obviously - requires that we have to somehow reach the content of the cells, hand those over to functions to let them do the magic. Did I say functions? Aren't those things that start with an equal sign and you select cells and they calculate stuff? Yes, those are the functions we are talking about. We will be creating functions like those.

Your basic function has a name, and might have some parameters to take. These parameters will be in most cases cells or cell ranges, some variables or results of other functions. I will talk about all these, but first, your function:

Code: Select all

function MAKE_TEXT_BOLD(text){
  return '[b]'+text+'[/b]';
}
Nothing fancy so far. Your function has a name (MAKE_TEXT_BOLD), has a parameter list (in this case, one parameter: text), and has a result, in which it returns you a text, surrounded by the phpBB tags to make it bold. The function name and the parameters are the "head" of the function, while the actual stuff it does is the "body". Please take note of the { and the } signs, these two symbols are the ones that surround a logical block in the script, similar to the [] and [/] in the forum. Each command we give out in the "body" is finished by a semicolon ( ; ), just for good measure.

How will we get to use this function?
- First, save the script you have just created in the >File< menu, by selecting >Save<. The scripts you will be creating here will be automatically usable in the spreadsheet you have been opened them from, but you could use them elsewhere too, more about that in a later tutorial.
- Navigate back to the spreadsheet. Select a cell you want to see the result in, and type in:

Code: Select all

=MAKE_TEXT_BOLD("This is a bold text!")
You will see that whatever you write in within the two " signs, will get the nice surround with the phpBB code. You might also notice that if you just type in whatever without the double quotes, you will end up with an #ERROR! in most of the cases. This is because the function is expecting a string (the coders way of saying "text"), and that is written with double quotation marks.

You will also see that your newly created function also accepts single cell addresses. Try it out! Write some text into your A1 cell. Then call your function with A1 in the belly, WITHOUT the quotation marks:

Code: Select all

=MAKE_TEXT_BOLD(A1)
But wait! Didn't I say the function expects a string parameter to come? And yet it accepts a cell? How is this working? The thing is, Spreadsheets converts the data at the Cell's address to a suitable type in the background, so your function actually does not know that it has been given a cell. It will only see the content within. This is really useful for simple data extraction, but will get trickier if we want to do deeper things.

I know what you are thinking: Can the function accept cell ranges too? Try it out! Write some stuff into B1, C1, and D1, and modify the previous function call like this:

Code: Select all

=MAKE_TEXT_BOLD(A1:D1)
You will see your content appear neatly inside, separated by commas. This is because cell ranges are translated into "arrays" (ordered sets of your data), which then are translated into strings in the simplest way possible: each element of the array is printed out, and then the next one is separated by a comma.

And that will be it for the first part! In the second one we will see how we can extract more information about a cell!

Re: Spreadsheet Scripting Tutorials

Posted: Tue Mar 13, 2018 6:35 pm
by Inspired
Tutorial 1
Script language, basic data extraction and usage - Part 2

With simple functions like the one made in part 1, we can create bold, italic, underlined text just by appending the appropriate phpBB codes to the sides of the text. But we are lazy. What we want to do is just style the cells themselves (make the text bold or italic there), and make a clever script that adds the proper phpBB tags! Wouldn't that be fun?

And to do this, we have to give the address of the cells as input. You might remember from the previous tutorial that if we give the address directly, e.g.: A1, the sheets will convert it to its content.

:paranoia: So we have to be sneaky! :paranoia:
We have to construct a string that describes our cell. Luckily there is a standard way to do this, and it even has a name, the "A1 notation". As the name suggests, it's almost as simple as typing A1. If we have filled any summing or calculating functions in spreadsheets already, we might remember that there are relative and absolute positions, which come handy when we drag a calculation to another cell, and the positions change accordingly.
For example if we type =$A1+$B1 into the cell C1, and then drag the bottom right corner to the cell below (C2), the formula magically changes to =$A2+$B2. What happened here is that we specified two cells, A1 and B1, and told the sheets' logic that we want the columns A and B absolute (unchanging), by putting a dollar sign ( $ ) in front of them. If we wanted the rows to be absolute instead, we could have written A$1+B$1.

I can see the smile on your face, because you can already see where we are heading. We can address every cell by their (absolute) position. We can even include on which sheet they are on! (And in more advanced cases we could even tell in which online available document they are on, provided we have access to those. But that will be another story...)
To tell which sheet the cell is on, just use the sheet's name and an exclamation mark ( ! ) before defining the position.

So for example if we want to refer to the A5 cell on the sheet Cats, we should create the string:

Code: Select all

"Cats!A5"
Note that we did not use the absolute positioning (with dollar signs), but we could have. It all depends on what we want to achieve.

Okay, this has been nice, but you have promised fun! :oops:
I know, I know, please bear with me just a little bit more. So we already know what we want to achieve, we still don't know the how yet. The help comes from the API of the spreadsheets, in the form of a function that "finds" the cell for us based on the string we have just created above.

Look at the following function:

Code: Select all

function IS_THIS_CELL_TEXT_BOLD(cellAddress){
  var fontWeight = SpreadsheetApp.getActive().getRange(cellAddress).getFontWeight();
  return (fontWeight == 'bold' );
}
This function has many new shiny things to play with:
  • We can define variables with the var keyword, and by giving them a name afterwards. Variables are small storages that hold information for us. Their content can be changed, which is good, because we don't always want to get stuck with the same stuff for all eternity... hence the name, variable.
  • We can assign a value to a variable with the single equal sign ( = ). This value can be something exact like a number, or a string. It can also be another variable, and it can also be the result of a function, which is used in the example above.
  • We can finally find our cells with the function SpreadsheetApp.getActive().getRange(cellAddress), where of course cellAddress is a variable that holds a string, like "Cats!A5", or whatever.
  • The function mentioned in the previous bulletpoint has many sweet additional information about our cell. Actually, what that function really returns is a "Range" object, which in turn has its own variables and functions. The full list of what it knows about a cell can be read here, this tutorial will be focusing on the basics.
  • We have a new result thingie, the boolean. It is a type which can be true or false, and as it suggests, it is really handy to evaluate expressions. What are expressions? Think about math: 4 > 3 is true, 5 > 6 is false, and 7 <= 8 (smaller or equal) is also true. Remember that we have used the single equation sign ( = ) to give values to variables, so we cannot use it to evaluate equations! We use double equation signs ( == ) instead! In the example above we compare the fontWeight variable to the text "bold". It returns true if they are the same, and false if they are not.
Okay, how can we use this to our advantage? We need a structure that can handle booleans, and make our program do something if the boolean is true, and do something else if it isn't. This structure - what a surprise - will be the "if". It's called a "control structure", because it controls how our program will behave. Notice the { and } signs again in the code just below! They surround the bits we want to separate. It is important to know that the first block after the if only gets to run if the expression in the parenthesis is true. If it's false, it gets skipped and the program continues with our commands after it. Also notice another keyword: else. It also opens a block in the code, which gets to run in case the expression for the if results in being false. An else statement only makes sense in the context of an existing if, but the existence of an else branch is not mandatory. We can decide we don't want to handle a case when our expression is false, then we can omit the usage of the else. Remember that the content of the if and the else branch are mutually exclusive, only one will run of those, based on the expression they have to evaluate. Also, we can further chain the else with additional ifs, which in turn can have their own else cases, and so on. For our own sanity, we won't do anything that complicated just now.

Let's rewrite our bold making function from the previous tutorial!

Code: Select all

function MAKE_TEXT_BOLD(cellAddress){
  var cellData = SpreadsheetApp.getActive().getRange(cellAddress).getDisplayValue();
  var fontWeight = SpreadsheetApp.getActive().getRange(cellAddress).getFontWeight();
  if ( fontWeight == 'bold' ){
    return '[b]'+cellData+'[/b]';
  } else {
    return cellData;
  }
}
Now, save your script and go back to the spreadsheet. Write something fun in a cell, for example into cell A1! Then go to another cell and use the previously made function with the address of the other cell, for example:

Code: Select all

=MAKE_TEXT_BOLD("A1")
And this is where the fun begins. Go to another cell, and write something else there, and make it bold with the spreadsheet's styling button. Now go to the cell which has our function, and make it point to this new cell! The phpBB tags will start to appear...

After some playing, you will notice two sad truths:
- Our new function doesn't work for cell ranges.
- The cell with our function updates seemingly erratically (only when we load the document or change the target address).

We will solve these issues in the next tutorials!

Until then, have fun with this function. You might want to extend to your needs:

Code: Select all

function GET_PHPBB_FORMATTED(cellAddress){
  var result = SpreadsheetApp.getActive().getRange(cellAddress).getDisplayValue();
  var fontStyle = SpreadsheetApp.getActive().getRange(cellAddress).getFontStyle();
  var fontWeight = SpreadsheetApp.getActive().getRange(cellAddress).getFontWeight();
  var fontColor = SpreadsheetApp.getActive().getRange(cellAddress).getFontColor();
  var fontSize = SpreadsheetApp.getActive().getRange(cellAddress).getFontSize();
  var fontDecoration = SpreadsheetApp.getActive().getRange(cellAddress).getFontLine();
  
  if (fontStyle == 'italic'){
    result = '[i]'+result+'[/i]';
  }
  if (fontWeight == 'bold'){
    result = '[b]'+result+'[/b]';
  }
  if (fontColor != '#000000'){
    result = '[color='+fontColor+']'+result+'[/color]';
  }
  if (fontSize != 10){
    result = '[size='+(fontSize*10)+']'+result+'[/size]';
  }
  if (fontDecoration == 'underline'){
    result = '[u]'+result+'[/u]';
  } else if (fontDecoration == 'line-through'){
    result = '[s]'+result+'[/s]';
  }
  return result;
}

Re: Spreadsheet Scripting Tutorials

Posted: Fri Mar 16, 2018 12:34 pm
by Inspired
Tutorial 1
Script language, basic data extraction and usage - Part 3

Where did we left off? Oh right. We have a neat function that can create the appropriate phpBB tags for a single cell. But we want more, we want to be able to read multiple cells, be it rows, columns, or bigger areas.

Somewhere in the middle of the previous tutorial we learned that we can get the Cell information back with the function SpreadsheetApp.getActive().getRange(cellAddress). As the name suggests, this should retrieve the entire range given inside, not only one cell, if the "cellAddress" parameter contains a range, and not a cell. And that is indeed the case, we only have to call the functions that fulfill our needs.

Let's take a look at the function we used to get the "bold-ness" of a cell:

Code: Select all

var fontWeight = SpreadsheetApp.getActive().getRange(cellAddress).getFontWeight();
Notice the last word: getFontWeight. If we read the documentation ( here ), we can see that it returns the top-left corner data. If we peek just a little bit below in the documentation, we can see that this function has a "plural" version: getFontWeights, which returns all cell information of the range. Please be aware of that the name of the function in itself does not guarantee that the return type will be something we would expect, it is just good practice to name our functions in an intuitive way.

In any case, since the Spreadsheets API is a professional product, we can safely assume that our functions have the "plural" versions to get the range data, we can check that in the documentation too. And here comes the main problem, which is the topic of this tutorial: the return values of these functions are something named like this: string[][]. What is that? We are sure it's not an empty phpBB code, so it's gotta be some kind of new type. Actually, it's not that new, it's a two dimensional array.

We have briefly met arrays in the first tutorial, where we passed a range into our function (not the address string, but the actual range), and I mentioned that it is translated into an array of data for the function. And I also said that arrays are actually ordered sets of data. These sets have dimensions, which are similar to the dimensions in maths, except here arrays can have only integer dimensions. The most common cases we use are 1 and 2 dimensions. I mentioned that these sets of data are ordered. Arrays order their data by indexing them. The very first item gets the index of 0, the next one gets 1 and so on.
From this, we can conclude that an array that has N items, will have its data indexed between 0 and N-1.

And this is where the brackets ( [ and ] ) come to play. They symbolize an array index of a dimension. We can refer to the item in the array by putting its index into the bracket. For example if we have an array named data and we want to get the very first item, and assign that to a variable named firstItem we would write:

Code: Select all

var firstItem = data[0];
I think you are already guessing that in case of a two dimensional array you can give two indexes, and that the second one depends on the first one, and you are wondering how are these translate to the spreadsheet tables. Well, the data we retrieve from a range by one of the "plural" named functions have their data indexed first by the row, and second by the column. So if your range starts in column A, ends in column C, and contains 2 lines, then you can refer to your data with an array[0 .. 1][0 .. 2], where [0][0] would be A1 and [1][2] would be C2. Please note that regardless of your starting column the array is always indexed from 0 and lasts as long as your column count. So an A1:C2 range will have the same [0 .. 1][0 .. 2] indexes as an F10:H11 range. Also, indexing out of the range of the array (for example using -1 or bigger than N-1) causes errors, so we have to stay within the bounds!

Great! Now we have only one thing left to do! We obviously do NOT want to type in each and every index by hand, partly because it seems like a lot of work, and partly because we might not even now first how big our data will be. So we need some kind of programming magic that will iterate through all these indexes, and gets the data for us.

One handy structure for this is the "for" structure. It looks something like this:

Code: Select all

for ( iterator setup; running condition ; iterator step ){
   do_something_with_an_array( array[ iterator ] );
}
We can see that similarly to the if structure, we have to define some running condition between the parenthesis, which then gets executed between the { } bracelets. We can also notice the "iterator" word and we strongly suspect that it will be the variable that will symbolize the index in our array. Since we have a 2 dimensional array, we have to use 2 for structures, one in the body of the other one, so we can iterate through all the data.

So, what comes into the head part, between the parenthesis?
  1. We have to define a name for our iterator. Classically, this tends to be i, j, k because in maths we generally use these to index vectors, and also because they are short so we don't have to write long variable names, but for the sake of understanding we will use the word "row" and "column" this time.
  2. We have to initialize this iterator with a value. Since we want to loop through an array, 0 looks like a good starting value.
  3. We have to define a stop condition. We obviously want to stop iterating when we reach the bounds of the array, so we have to get the array limits somehow.
  4. We have to define a method to change our iterator. Since we want to loop through all the indexes, simply increasing by 1 every time seems like a good solution.
To get the limits of an array, we can call its length attribute (it is NOT a function, so no parenthesis!). Each dimension has its own length, so if we have an array named data, and it has two dimensions, then we get the limits with as "data.length" and "data[0].length". Notice that indexes are actually 1 smaller than these.

This looks a bit cumbersome, luckily we have a better readable solution! Remember that we actually want to get the dimensions of our range, which should know its size. We can get this by calling range.getNumColumns() and range.getNumRows().

The finishing touch is defining the step of our iterator. We could write row = row + 1, but there is a shorthand for this: row++.

So our for loop will be something like this:

Code: Select all

for ( row = 0; row < range.getNumRows(); row++){
  for (column = 0; column < range.getNumColumns(); column++){
    do_something_with_our_array( data[row][column] );
  }
}
There will be some tricks ahead, so I'm finishing this tutorial here. Stay ready for the next post for the actual usage! (Or figure it out by yourself, if you have the time and the will to do it. ;) You can compare your solutions afterwards. )

Re: Spreadsheet Scripting Tutorials

Posted: Sun Mar 18, 2018 7:13 pm
by Inspired
Tutorial 1
Script language, basic data extraction and usage - Part 4

Before we create our for loop to get through all the cells of the selected range, let's talk about structuring our code a little bit. Right now we have a big function that reads a cell and formats it based on the several attributes we read:

Code: Select all

function GET_PHPBB_FORMATTED(cellAddress){
  var result = SpreadsheetApp.getActive().getRange(cellAddress).getDisplayValue();
  var fontStyle = SpreadsheetApp.getActive().getRange(cellAddress).getFontStyle();
  var fontWeight = SpreadsheetApp.getActive().getRange(cellAddress).getFontWeight();
  var fontColor = SpreadsheetApp.getActive().getRange(cellAddress).getFontColor();
  var fontSize = SpreadsheetApp.getActive().getRange(cellAddress).getFontSize();
  var fontDecoration = SpreadsheetApp.getActive().getRange(cellAddress).getFontLine();
 
  if (fontStyle == 'italic'){
    result = '[i]'+result+'[/i]';
  }
  if (fontWeight == 'bold'){
    result = '[b]'+result+'[/b]';
  }
  if (fontColor != '#000000'){
    result = '[color='+fontColor+']'+result+'[/color]';
  }
  if (fontSize != 10){
    result = '[size='+(fontSize*10)+']'+result+'[/size]';
  }
  if (fontDecoration == 'underline'){
    result = '[u]'+result+'[/u]';
  } else if (fontDecoration == 'line-through'){
    result = '[s]'+result+'[/s]';
  }
  return result;
}
We can see that it can be divided into two parts:
  1. Reading the cell information based on the input.
  2. Creating the string based on the information we extracted.
We can also see that we are calling the same function to get the range of the sheet over and over again. It makes the code ugly, prone to errors (we have to change every line if we make a change), and a slightly bit slower too.

As a start, let's clean up that, along with renaming the "result" variable. (It will not be the result later, so that is kind of misleading):

Code: Select all

function GET_PHPBB_FORMATTED(cellAddress){
  var range = SpreadsheetApp.getActive().getRange(cellAddress);

  var value = range.getDisplayValue();
  var fontStyle = range.getFontStyle();
  var fontWeight = range.getFontWeight();
  var fontColor = range.getFontColor();
  var fontSize = range.getFontSize();
  var fontDecoration = range.getFontLine();
Second, we can see that the actual formatting of the text is always the same, regardless of how many input cells there are. We strongly suspect that we should not write it down multiple times, and yet it's quite large to even include it once, especially if it's part of a larger function. We can also think that it should be reusable in other parts of the code, because formatting is always handy (and that is our main goal with this tutorial anyway).

So let's move the actual formatting to a new function! As an added bonus, we can use this new function independently to format our cells as we wish, by calling it just like we called our MAKE_TEXT_BOLD earlier...

Code: Select all

function format_by_parameters(text, fontStyle, fontWeight, fontColor, fontSize, fontDecoration){
  if (fontStyle == 'italic'){
    text = '[i]'+text+'[/i]';
  }
  if (fontWeight == 'bold'){
    text = '[b]'+text+'[/b]';
  }
  if (fontColor != '#000000'){
    text = '[color='+fontColor+']'+text+'[/color]';
  }
  if (fontSize != 10){
    text = '[size='+(fontSize*10)+']'+text+'[/size]';
  }
  if (fontDecoration == 'underline'){
    text = '[u]'+text+'[/u]';
  } else if (fontDecoration == 'line-through'){
    text = '[s]'+text+'[/s]';
  }
  return text;
}
With these two changes, our previous function will look like this:

Code: Select all

function GET_PHPBB_FORMATTED(cellAddress){
  var range = SpreadsheetApp.getActive().getRange(cellAddress);

  var value = range.getDisplayValue();
  var fontStyle = range.getFontStyle();
  var fontWeight = range.getFontWeight();
  var fontColor = range.getFontColor();
  var fontSize = range.getFontSize();
  var fontDecoration = range.getFontLine();

  return format_by_parameters(value, fontStyle, fontWeight, fontColor, fontSize, fontDecoration);
}
Doesn't it look a lot neater? Also, when we describe our next goal, which is making the function support more cells, the solution kind of presents itself, just by reading the code.

We have two things to do:
  1. Change the data gathering to collect all the cells instead of only one.
  2. Add a for loop to go through all the extracted data
In the previous tutorial we have seen that getting all the cell formatting information is as simple as adding an "s" at the end of the functions. For good measure, we are also renaming our variables to the plural forms, so we don't forget that they contain multiple cell data now:

Code: Select all

function GET_PHPBB_FORMATTED(cellAddress){
  var range = SpreadsheetApp.getActive().getRange(cellAddress);
  
  var values = range.getDisplayValues();
  var fontStyles = range.getFontStyles();
  var fontWeights = range.getFontWeights();
  var fontColors = range.getFontColors();
  var fontSizes = range.getFontSizes();
  var fontDecorations = range.getFontLines();
And second, we write the for structure that will loop through this data, and will append the formatted text to a result string, that will get shown in our cell.

Code: Select all

  var rowCount = range.getNumRows();
  var columnCount = range.getNumColumns();
  var endResult = "";
  
  for( row = 0; row < rowCount; row++ ){
    if(row > 0){
      endResult += "\n";
    }
    for ( column = 0; column < columnCount; column++ ){
      if ( column > 0 ){
        endResult += ", ";
      }
      endResult += format_by_parameters(
            values[row][column],
            fontStyles[row][column],
            fontWeights[row][column],
            fontColors[row][column],
            fontSizes[row][column],
            fontDecorations[row][column] );
    }
  }
  return endResult;
}
If we join the two parts together, we get a neat function that goes through all the given cell range, and puts each cell's content into the appropriate phpBB format. Moreover, for easier readability it will put a linebreak after each row, and separates the column content with a comma. Good job!

Let's play around with that function, by creating all kinds of formatted cells and defining all kinds of ranges!

We will see that finally now we can support input like "A1" or "A1:B10" with equal success. Except, there is still something wrong. We still have to define the exact edges of the range! It makes the usage of the function a bit awkward, because if we add a new column or row, we have to readjust the range! If we are lazy and define a range that consists of either a full row or column (e.g.: "A3:B" is a range that contains the full columns A and B starting from the 3rd row), our function will format all the cells, even the empty ones! This could be a desired behavior, but more often than not we only want to format cells that are actually have any meaningful content.

A quick glance to the documentation tells us that the range object might not know if it consists of empty or filled cells. However, the sheet that contains it has a function that is named getDataRange(), which returns a range that actually has data inside. We will use this to our advantage, instead of trying to create a function that would decide this for us, probably creating a messy code.

So we have - again - two things to solve:
  1. Get the actually filled data range for our function
  2. Intersect this filled range with the range defined by the input
The first one is quite simple. Notice that we can get the active document, sheet, range, cell by adding the word "Active" into the appropriate functions. The active item is what is selected by the user at the time of the function call, or what gets activated by the function in a previous call. So all we have to do is activating the area that is queried by the user, and selecting the data range. Notice that this selection is invisible to the user, they won't see us manipulating the selection.

This is the code we get the active data range with, based on the user input:

Code: Select all

function GET_PHPBB_FORMATTED(cellAddress){
  var range = SpreadsheetApp.getActive().getRange(cellAddress);

  range.activate();
  var dataRange = SpreadsheetApp.getActiveSheet().getDataRange();
Now all we have to do is get the intersection of the two ranges. If we think about it, it is not that hard: all we have to do is decide whether the filled range's column/row count is bigger or the one given as input. We will choose the smaller ones, because those will be filled. We also have to be aware of the fact that the user might not select A1 as the top left corner for the input range, so we have to adjust this "offset" when we decide about the actually visited column and row count. We can easily get the range's starting row and column with the functions getRow() and getColumn(). Remember that these rows and columns start getting numbered by 1, whereas our first index is 0, so we have to subtract 1 from it to match the array with the cells.
To decide for the smaller values, we can use the "min" math function, which does what we expect: selects the smallest value of the given inputs.

Changing the rowCount and columnCount variables in the code above will give us the "filled" range:

Code: Select all

  var rowCount = Math.min( range.getNumRows() , dataRange.getNumRows()-(range.getRow()-1) );
  var columnCount = Math.min( range.getNumColumns() , dataRange.getNumColumns()-(range.getColumn()-1) );
Let's reassemble all the above in our previous function!

Code: Select all

function GET_PHPBB_FORMATTED(cellAddress){
  var range = SpreadsheetApp.getActive().getRange(cellAddress);
  
  var values = range.getDisplayValues();
  var fontStyles = range.getFontStyles();
  var fontWeights = range.getFontWeights();
  var fontColors = range.getFontColors();
  var fontSizes = range.getFontSizes();
  var fontDecorations = range.getFontLines();
  
  range.activate();
  var dataRange = SpreadsheetApp.getActiveSheet().getDataRange();
  
  var rowCount = Math.min(range.getNumRows(), dataRange.getNumRows()-(range.getRow()-1));
  var columnCount = Math.min(range.getNumColumns(), dataRange.getNumColumns()-(range.getColumn()-1));
  var endResult = "";
  
  for( row = 0; row < rowCount; row++ ){
    if(row > 0){
      endResult += "\n";
    }
    for ( column = 0; column < columnCount; column++ ){
      if ( column > 0 ){
        endResult += ", ";
      }
     endResult += format_by_parameters(
            values[row][column],
            fontStyles[row][column],
            fontWeights[row][column],
            fontColors[row][column],
            fontSizes[row][column],
            fontDecorations[row][column] );
    }
  }
  return endResult;
}
This has been quite long for a tutorial, huh. See you next time!

Re: Spreadsheet Scripting Tutorials

Posted: Tue Apr 03, 2018 4:22 pm
by Inspired
Tutorial 2

On-Demand Scripts and Custom User Interfaces - Part 1

If you have followed the tutorials so far, you are already familiar with creating simple functions that do the formatting for you. There is just this one teeeny little problem:
The functions you are using in your cells refuse to refresh automatically in many cases, and using them this way can be really cumbersome.

The following tutorials are meant to help you regarding these problems.

Giving "rights" to your code

To enable the advanced features of scripts, Google will ask you to give some privileges to them. The following tutorials will require two privileges to be given:
  • View and manage your spreadsheets in Google Drive
  • Display and run third-party web content in prompts and sidebars inside Google applications


These are needed for you to progress, so please allow them for your scripts. Remember that anyone with only viewing privileges won't be able to see / run your scripts, but if they can make a copy of your document, they can edit that one freely. DO NOT GIVE editing rights to anyone to your scripts unless you trust them really, really much. Your account is on stake, so it's easier to just don't give anyone anything. The best solution is to have a separate private document with scripts, and a public "readable" document for your players. You can update that one with scripts so it won't be a hassle to keep in sync.


Google will ask for permissions along the way. You can bestow the above stated rights in the "Advanced" part of the pop-up, if you do not wish to submit your scripts for checking or building an add-on. (If you do your tutorials 1 by 1, only the first permission will be asked here, the second in a further one.)

Creating a MENU

Running scripts with a click is much more comfortable than writing the functions into cells. Which means, we need a visible place to be able to "click" our scripts.
The Spreadsheets offers us two simple ways to do this:
  1. Creating a button / image and running the specified script when clicking on it
  2. Creating a top level menu / add-on menu with (sub)menu items.
While the first one looks appealing, I'd suggest not to do it. You want to keep your sheets "clean", tracking all the information is enough, no need to complicate the layout with buttons and whatnot. Unless, of course, you want some "interactive" surface for your players or something.

Creating a menu has an added benefit of being unreachable for viewers only. Be it a top level menu, or an add-on menu, users with only viewing rights won't be able to use them, so you don't have to worry about it. You can only create top level menus in the document the script is bound to, everywhere else your scripts will be "add-ons" and will be placed under the Add-on menu, regardless if you intended to put them there or as top level.

Let's create a menu!

To do this, we need the UI (User Interface) related functions of the Spreadsheets API. To modify the UI (like adding a menu) there are some special functions that have to be created. You can create these functions in your current file, but I suggest to create a new file within the same project, dedicated only to UI machinations. So you will know later where to find your things in a hurry. Don't worry, the scripts will know about each other within the project, so you can refer to the already made functions freely, without any further actions.

To create a menu, you have to use the onOpen() function, which gets called when you open the document. Let's see a simple example:

Code: Select all

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  
  ui.createMenu('phpBB Scripts')
      .addItem('Get Player Names', 'listPlayers')
      .addToUi();
}

function listPlayers() {
  var text = printPlayerNames(SpreadsheetApp.getActive().getRange('sample1!B2:B7').getDisplayValues());
  Browser.msgBox(text);
}
Things to know about the code above:
  • The SpreadsheetApp.getUi() will return you a handler to reach the user interface. I assigned that to a variable for further usage and readability.
  • The createMenu('Name') command starts building your custom menu. It will be a top level menu this way, if you prefer to hide your scripts into an add-on, you can use the createAddonMenu() instead.
  • The addItem part creates an item in your menu. The first parameter is the displayed name in the menu, the second parameter is the name of the function that should be run. Notice that the name does not have a parenthesis or a parameter list, so you have to use a tricky "wrapping" function that will give the input parameters to the actual function you wish to run.
  • You can also add submenus and separator lines, if you wish. Feel free to check out the official guide here.
  • The addToUi() finishes the construction of your menu, which should be now ready to use. Using many functions in chains (following each other with a dot and being actually one big function) is called in this case a "Builder Pattern". It generally consists of a starting function (createMenu), setup functions (addItem), and a finalizing function (addToUi). It is typical in cases where you have to dynamically build something, generally UI elements (menus, dialogs) with a preset of options. Don't worry, you DO NOT HAVE TO KNOW this or the inside workings or creating such things to be able to do useful things in these tutorials. I just wanted to explain a little in case you were wondering why the function looked weird.
  • As stated above in the addItem part, you need a function which will be called by the menu item. Tips for this will be below.
  • In the example above, instead of filling a cell we put our results in a neat message box by using the Browser.msgBox(text) command. Again, to avoid cluttering. More about this in the next tutorial!
And now, back to the last problem.

As you probably remember, all the text formatting functions we have been creating so far had input parameters. But we cannot fill these in the menu declaration, we need a new wrapper function for each of them to connect the missing links. How to create these depends on what our own functions expect as input. We have talked about input types to great lengths in the Tutorial 1 series before, check them as a refresher if you need it.

Let's see some solutions:
  • If the function you want to call expects the exact content to work on (like the simple phpBB tag appender in the earliest tutorial), you can use the Range object's getDisplayValues() to fill this information, just like you did in the inside of the further functions. PLEASE NOTE that in the wrapping function you can use the SpreadsheetApp.getActive().getRange('A1:B2') syntax to "lock" a certain range, or you can get the selected range with SpreadsheetApp.getActiveRange() easily. In this case, you have to select an area of the spreadsheet first and click the menu button afterwards. It feels natural and simple, right? (On a side note: if you rewrite your original function to work only with selected ranges and it needs no input parameters it will make wrapping easier.)
  • If your function works with the address of a range, because it reads additional cell information (like the very last parts of Tutorial 1), you can get that by calling the getA1Notation() function of the range, for example: SpreadsheetApp.getActiveRange().getA1Notation(). That name seems familiar, right? We mentioned it when talking about adresses...
  • You could try to rewrite your original functions to expect actual range objects, and then only inside select their values or addresses. This will make them a little bit unusable in the sheets as "basic" functions, but can reduce the bulkiness of the wrapper functions that call them. Since you will generally like to have your functions neatly in menus or sidebars and don't want to type them into cells, you might consider rewriting them. Or create new ones tailored specifically for menu usage.
And that is it for today!
All times are UTC-07:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited