Installing/importing NPM Packages into your Google App Script Project | Blog | Mani Kumar

While Google Apps Script (GAS) provides a plethora of functionalities out of the box, sometimes there’s a need to leverage the vast ecosystem of NPM packages. This guide will help you seamlessly incorporate NPM packages into your GAS project.

Prerequisites:

Node.js and NPM installed on your machine.
A Google Apps Script project.
Basic familiarity with ES6+ syntax.

Example Scenario:

For demonstration purposes, we’ll integrate two noteworthy NPM packages:

to-words: A handy package that converts numbers into words. Whether you’re looking to convert 123 into “one hundred and twenty-three” or require the number in a currency format like “one hundred and twenty-three dollars,” to-words has got you covered. This can be especially useful in applications where numeric values need to be represented in a more human-readable format, such as invoice generation, checks, or educational tools.
dayjs: A lightweight alternative to Moment.js, dayjs offers a rich set of functionalities to manipulate, format, and compute dates and times without the bloat. It’s an invaluable tool when dealing with date operations, whether it’s for formatting dates for user-friendly displays, calculating time differences, or handling multiple time zones.

Step-by-Step Process:

Initialize NPM:
In your local project directory:
```
npm init -y
```
Install Required Packages:
Install the packages that you’d like to use:
```
npm install to-words dayjs
```

Set Up Your Entry Point (index.js):

Create an index.js file and import your required functions or classes.

import { ToWords } from 'to-words';
import dayjs from 'dayjs';

export { ToWords, dayjs };

Bundling:
Since GAS doesn’t recognize ES6 imports, we need to bundle our code. We’ll use esbuild for this. First, install it:
```
npm install esbuild
```
Next, add a bundle script to your package.json:
```
"scripts": {
  "bundle": "npx esbuild index.js --bundle --global-name=npmBundle --outfile=dist/bundle.js --format=iife"
}
```
Run the bundle script:
```
npm run bundle
```
Transfer to GAS:
Open the dist/bundle.js file, copy its contents, and paste it into a file you can maybe call npmBundle.gs file in the GAS editor. This file acts as a library containing the functionalities of to-words and dayjs. Each time you update your bundle locally, you need to replace this file on Google App Script.

Use in GAS:

In another .gs file (e.g., Code.gs), you can now leverage the bundled NPM packages:

function someFunction() {
  // Using to-words
  var toWordsInstance = new npmBundle.ToWords();
  var number = 12345;
  var words = toWordsInstance.convert(number, {currency: true, localeCode: "en-US"});
  Logger.log(words);

  // Using dayjs
  var date = npmBundle.dayjs().format('YYYY-MM-DD');
  Logger.log(date);
}

Bonus: Using NPM Packages as Custom Functions in Google Sheets

One of the most impressive features of Google Apps Script is the ability to create custom functions for Google Sheets. Once you have bundled and incorporated your NPM packages, you can easily expose them as formulas to be used directly inside your spreadsheet cells.

Here’s how you can implement to-words functionalities as custom Google Sheets functions:

1. Utility Function:

First, create a utility function that ensures you’re always working with a float value.

function toFloat(value) {
  if (typeof value === 'string') {
    const floatValue = parseFloat(value);
    if (!isNaN(floatValue)) {
      return floatValue;
    }
  }
  return value; // return original value if not convertible to float
}

2. Custom Sheet Functions:

Next, we’ll create custom functions that use to-words to convert currency values to words:

function dollarToWords(currencyText) {
  var toWordsInstance = new npmBundle.ToWords({
    localeCode: 'en-US',
    converterOptions: {
      currency: true,
    }
  });
  return toWordsInstance.convert(toFloat(currencyText));
}

function rupeeToWords(currencyText) {
  var toWordsInstance = new npmBundle.ToWords({
    localeCode: 'en-IN', // Adjusted localeCode for INR
    converterOptions: {
      currency: true,
    }
  });
  return toWordsInstance.convert(toFloat(currencyText));
}

3. Using in Google Sheets:

After saving your Google Apps Script, return to your Google Sheet. In any cell, you can now use your custom functions:

=dollarToWords("1234.56")
=rupeeToWords(A1)

The first formula will convert the text “1234.56” to its word representation in USD, while the second will convert the value in cell A1 to its word representation in INR.

Caveats and Notes:

Not all NPM packages will be compatible with the GAS environment due to differences between Node.js and Google Apps Script. Testing is crucial.
Bundling might increase the size of your GAS project. Make sure you’re not exceeding any project size limits.
Regularly update the NPM packages and re-bundle to ensure you have the latest security patches and features.

Conclusion:

While GAS doesn’t natively support NPM packages, with some setup, you can access a vast array of functionalities provided by the NPM ecosystem.

This guide demonstrated the process ing to-words and dayjs, but the methodology can be applied to most NPM packages.

Happy coding!

Pssst: If you’d like the automate the process of pushing your bundled code to your app script project, you can check out clasp.