Lecture: Learning custom functions in Google Sheets, javascript ver. 4

Last Updated on July 9, 2022 by shibatau

The categories has been changed: from GoogleSheets to Lecture.

I. What shall we learn?

We will learn custom functions mainly from the following tutorial:

Getting a custom function from the Google Workspace Marketplace

II. Getting custom functions from the Google Workspace Marketplace

  1. At the top of your spreadsheet, click Extensions>Add-ons > Get add-ons.
  2. Once the Google Workspace Marketplace opens, click the search box at the top. Type the name of the add-on or “custom function” and press Enter.
  3. Click Install to install a custom function add-on you’re interested in.
  4. A dialog box might tell you that the add-on requires authorization. If so, click Allow.
  5. The add-on becomes available in the spreadsheet.

I have installed numbertext in the following way.

III. Creating custom functions using JavaScript

I’ve got a script from the tutorial showed above.

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Ternary operator, condition ? value if true : value if false, is explained here:

JavaScript Ternary Operator

IV. Some examples for practical use

1.Get the data on the web page and format the table with a custom function

You can see my sheets here:

https://docs.google.com/spreadsheets/d/1tamA-yxQL4lU5xnaIFXrQIwXUJacSto-nUmMw3O2rBQ/edit?usp=sharing

You can learn how to write scripts here:

Google Spreadsheets Scripts: An Easy Tutorial to Level Up Your Spreadsheets

I wand to change the formats of the table in the following way using a custom function.

The scripts are here:

function customFormat() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var header = sheet.getRangeList(['A1:G1']);
  var table = sheet.getDataRange();
  var score = sheet.getRangeList(['B2:C201']);
  
  sheet.getRangeList(['F2:F201']).setValue("NA");
  header.setFontStyle("italic");
  header.setFontColor("white");
  header.setBackground("blue");
  header.setHorizontalAlignment("center");

  table.setFontSize(16);
  table.setHorizontalAlignment("center");
  score.setHorizontalAlignment("right");
  table.setBorder(true,true,true,true,false,true);

}

Now I’ve got the result here.

To be continued.

About shibatau

I was born and grown up in Kyoto. I studied western philosophy at the University and specialized in analytic philosophy, especially Ludwig Wittgenstein at the postgraduate school. I'm interested in new technology, especially machine learning and have been learning R language for two years and began to learn Python last summer. Listening toParamore, Sia, Amazarashi and MIyuki Nakajima. Favorite movies I've recently seen: "FREEHELD". Favorite actors and actresses: Anthony Hopkins, Denzel Washington, Ellen Page, Meryl Streep, Mia Wasikowska and Robert DeNiro. Favorite books: Fyodor Mikhailovich Dostoyevsky, "The Karamazov Brothers", Shinran, "Lamentations of Divergences". Favorite phrase: Salvation by Faith. Twitter: @shibatau

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.