How to Request Payments with Stripe Checkout and Google Sheets

Stripe Payment Links

Stripe payment links make it easy for you to accept credit card payments from customers anywhere in the world without even having a website. You can use the Stripe dashboard to generate payment links and then send the links over email, WhatsApp, SMS, or share them on your social media pages.

A limitation of Stripe Payment links is that you can only generate them manually. Stripe has a feature-rich API but it doesn’t allow you to generate payment links automatically.

If you are looking to generate custom payment links for Stripe in bulk and send them to your customers, you can consider using Stripe Checkout. These are payment forms hosted on the Stripe website and allow you to collect only payments your customers.

It is important to note that Stripe Checkout sessions will automatically expire after 24 hours. As an alternative, you can use the Stripe API to generate invoices and email the invoice link to your customers.

Stripe API Key

To get started, open your Stripe dashboard, go to Developers > API Keys > Created restricted API key.

Give your key a descriptive name, choose the Write permission under Checkout Sessions and click Create key.

Next, make a copy of the Stripe Google Sheet in your Google Drive. Go to Tools > Script Editor and replace the Stripe API Key with the key generated in the previous step. Then, click on the Run menu once to authorize the script with your Google Account.

Switch to the Google Sheet and you can now use the custom Google Sheets function STRIPE() to generate Stripe Checkout sessions for accepting online payments.

If you would like to generate payment links for multiple rows in the Google Sheet, just write the formula in the first row and drag the crosshairs to the other rows as show in the demo below. Array Formulas are not supported yet.

Stripe Payment Links

How Stripe Checkout Works with Google Sheets

If you are curious to know how integration of Google Sheets and Stripe works, the answer is Google Apps Script. The underlying code invokes the Stripe API with your secret API key and writes the generated checkout session links in the Google Sheet.

The custom Google Sheets function uses the built-in caching service of Apps Script to reduce latency and improve performance. The code can be extended to accept recurring payments for subscriptions.

/**
 *
 *  Author  Amit Agarwal
 *  Email   amit@labnol.org
 *  Web     https://digitalinspiration.com/
 *
 **/

const STRIPE_API_KEY = "<< Stripe API Key >>";
const STRIPE_SUCCESS_URL = "https://digitalinspiration.com";
const STRIPE_CANCEL_URL = "https://digitalinspiration.com";

/**
 * Generate Stripe payment links in Google Sheets
 *
 * @param {number} amount The amount to be paid using Stripe
 * @param {string} currency The 3-letter currency code (optional)
 * @param {string} description A short description of the item name (optional)
 * @return Stripe checkout session link
 * @customfunction
 */

const STRIPE = (amount, currency, description) => {
  const input = {
    "line_items[0][price_data][currency]": currency || "USD",
    "line_items[0][price_data][product_data][name]": description || "Name",
    "line_items[0][price_data][unit_amount]": Math.ceil(amount * 100),
    "line_items[0][quantity]": 1,
  };

  const cacheKey = JSON.stringify(input);

  const cachedLink = CacheService.getScriptCache().get(cacheKey);

  if (cachedLink) return cachedLink;

  const params = {
    cancel_url: STRIPE_CANCEL_URL,
    success_url: STRIPE_SUCCESS_URL,
    mode: "payment",
    billing_address_collection: "required",
    "payment_method_types[]": "card",
    ...input,
  };

  const payload = Object.entries(params)
    .map(([key, value]) =>
      [encodeURIComponent(key), encodeURIComponent(value)].join("=")
    )
    .join("&");

  const response = UrlFetchApp.fetch(
    "https://api.stripe.com/v1/checkout/sessions",
    {
      method: "POST",
      headers: {
        Authorization: `Bearer ${STRIPE_API_KEY}`,
        "Content-Type": "application/x-www-form-urlencoded",
      },
      payload,
      muteHttpExceptions: true,
    }
  );

  const { url, error } = JSON.parse(response);

  if (url) {
    CacheService.getScriptCache().put(cacheKey, url, 21600);
  }

  return error ? error.message : url;
};

You can use Mail Merge with Gmail to request online payments from your customers over email. You may also use Document Studio to create PDF invoices and embed the payment links directly in the customer’s invoice.

How to Request Payments with Stripe Checkout and Google Sheets

Stripe Payment Links

Stripe payment links make it easy for you to accept credit card payments from customers anywhere in the world without even having a website. You can use the Stripe dashboard to generate payment links and then send the links over email, WhatsApp, SMS, or share them on your social media pages.

A limitation of Stripe Payment links is that you can only generate them manually. Stripe has a feature-rich API but it doesn’t allow you to generate payment links automatically.

If you are looking to generate custom payment links for Stripe in bulk and send them to your customers, you can consider using Stripe Checkout. These are payment forms hosted on the Stripe website and allow you to collect only payments your customers.

It is important to note that Stripe Checkout sessions will automatically expire after 24 hours. As an alternative, you can use the Stripe API to generate invoices and email the invoice link to your customers.

Stripe API Key

To get started, open your Stripe dashboard, go to Developers > API Keys > Created restricted API key.

Give your key a descriptive name, choose the Write permission under Checkout Sessions and click Create key.

Next, make a copy of the Stripe Google Sheet in your Google Drive. Go to Tools > Script Editor and replace the Stripe API Key with the key generated in the previous step. Then, click on the Run menu once to authorize the script with your Google Account.

Switch to the Google Sheet and you can now use the custom Google Sheets function STRIPE() to generate Stripe Checkout sessions for accepting online payments.

If you would like to generate payment links for multiple rows in the Google Sheet, just write the formula in the first row and drag the crosshairs to the other rows as show in the demo below. Array Formulas are not supported yet.

Stripe Payment Links

How Stripe Checkout Works with Google Sheets

If you are curious to know how integration of Google Sheets and Stripe works, the answer is Google Apps Script. The underlying code invokes the Stripe API with your secret API key and writes the generated checkout session links in the Google Sheet.

The custom Google Sheets function uses the built-in caching service of Apps Script to reduce latency and improve performance. The code can be extended to accept recurring payments for subscriptions.

/**
 *
 *  Author  Amit Agarwal
 *  Email   amit@labnol.org
 *  Web     https://digitalinspiration.com/
 *
 **/

const STRIPE_API_KEY = "<< Stripe API Key >>";
const STRIPE_SUCCESS_URL = "https://digitalinspiration.com";
const STRIPE_CANCEL_URL = "https://digitalinspiration.com";

/**
 * Generate Stripe payment links in Google Sheets
 *
 * @param {number} amount The amount to be paid using Stripe
 * @param {string} currency The 3-letter currency code (optional)
 * @param {string} description A short description of the item name (optional)
 * @return Stripe checkout session link
 * @customfunction
 */

const STRIPE = (amount, currency, description) => {
  const input = {
    "line_items[0][price_data][currency]": currency || "USD",
    "line_items[0][price_data][product_data][name]": description || "Name",
    "line_items[0][price_data][unit_amount]": Math.ceil(amount * 100),
    "line_items[0][quantity]": 1,
  };

  const cacheKey = JSON.stringify(input);

  const cachedLink = CacheService.getScriptCache().get(cacheKey);

  if (cachedLink) return cachedLink;

  const params = {
    cancel_url: STRIPE_CANCEL_URL,
    success_url: STRIPE_SUCCESS_URL,
    mode: "payment",
    billing_address_collection: "required",
    "payment_method_types[]": "card",
    ...input,
  };

  const payload = Object.entries(params)
    .map(([key, value]) =>
      [encodeURIComponent(key), encodeURIComponent(value)].join("=")
    )
    .join("&");

  const response = UrlFetchApp.fetch(
    "https://api.stripe.com/v1/checkout/sessions",
    {
      method: "POST",
      headers: {
        Authorization: `Bearer ${STRIPE_API_KEY}`,
        "Content-Type": "application/x-www-form-urlencoded",
      },
      payload,
      muteHttpExceptions: true,
    }
  );

  const { url, error } = JSON.parse(response);

  if (url) {
    CacheService.getScriptCache().put(cacheKey, url, 21600);
  }

  return error ? error.message : url;
};

You can use Mail Merge with Gmail to request online payments from your customers over email. You may also use Document Studio to create PDF invoices and embed the payment links directly in the customer’s invoice.

How to Delete Blank Rows from Tables in your Google Documents

The Document Studio add-on helps you generate Google Documents from data in Google Sheets and Google Form responses. You can create a template in Google Docs and the add-on will replace the placeholders with answers submitted in the Google Form response.

This approach may however create a lot of blank rows in the table for answers that have no response in Google Forms. To give you an example, if the user has not answered the Age question, the generated document will have a row for the {{Age}} question but with a blank value.

Google Docs Remove Table

Remove Blank Rows in Google Docs

With the help of Google Apps Script, we can easily pull all tables that are contained in the body of a Google Document, iterate through each row in the table and, if there’s no value in the row, we can safely remove the row from the table.

Inside your Google Document, go to the Tools menu, choose Script Editor and paste the following code. Go to the Run menu and choose RemoveBlankRows from the dropdown to run the script.

const removeBlankRows = () => {
  // Replace all whitespaces and check if the cell is blank
  const isBlankCell = (text = "") => !text.replace(/\s/g, "");

  // Does the row have any data other than in column 1 (header)
  const rowContainsData = (row) => {
    const columnCount = row.getNumCells();
    let rowHasFilledCell = false;
    for (
      let columnIndex = 1;
      columnIndex < columnCount && !rowHasFilledCell;
      columnIndex += 1
    ) {
      const cellValue = row.getCell(columnIndex).getText();
      if (!isBlankCell(cellValue)) {
        rowHasFilledCell = true;
      }
    }
    return rowHasFilledCell;
  };

  // Get the current document
  const document = DocumentApp.getActiveDocument();

  document
    .getBody()
    .getTables()
    .forEach((table) => {
      const rowCount = table.getNumRows();
      for (let rowIndex = rowCount - 1; rowIndex >= 0; rowIndex -= 1) {
        const row = table.getRow(rowIndex);
        if (isBlankCell(row.getText()) || !rowContainsData(row)) {
          // Remove the row from the Google Docs table
          table.removeRow(rowIndex);
        }
      }
    });

  // Flush and apply the changes
  document.saveAndClose();
};

Delete Blank Table Rows in Google Slides

You can use the same technique to remove blank rows from tables that are contained in your Google Slide presentation.

If your Google Slides table uses merged cells, you may want to check merge status of a cell with the SlidesApp.CellMergeState.MERGED enum.

const removeBlankRows = () => {
  // Get the current document
  const presentation = SlidesApp.getActivePresentation();

  presentation.getSlides().forEach((slide) => {
    slide.getTables().forEach((table) => {
      const rowCount = table.getNumRows();
      for (let rowIndex = rowCount - 1; rowIndex >= 0; rowIndex -= 1) {
        const row = table.getRow(rowIndex);
        const cellCount = row.getNumCells();
        let rowHasFilledCell = false;
        for (
          let cellIndex = 1;
          cellIndex < cellCount && !rowHasFilledCell;
          cellIndex += 1
        ) {
          const cellValue = row.getCell(cellIndex).getText().asString();
          if (cellValue.trim() !== "") {
            rowHasFilledCell = true;
          }
        }

        if (!rowHasFilledCell) {
          row.remove();
        }
      }
    });
  });

  // Flush and apply the changes
  presentation.saveAndClose();
};

How to Request Payments with Razorpay and Google Sheets

Razorpay is a popular payment gateway in India that allows you to accept online payments from customers anywhere in the world. Your customers can pay with credit cards, debit cards, Google Pay, Walmart’s PhonePe and other UPI apps.

Google Sheets + Razorpay

Razorpay, similar to Stripe, offers a simple no-code tool for generating payment links that you can share with customers over SMS, WhatsApp, or email. When a customer clicks on the link, they are redirected to a secure checkout page hosted on Razorpay where they can can make the payment using their preferred payment method.

Here’s a sample payment link generated with Razorpay - https://rzp.io/i/6uBBFWBfv

It takes one easy step to generate payment links with Razorpay. Sign-in to your Razorpay account, go to the Payment Links section and click on the Create Payment Link button.

The built-in wizard is perfect for generating a few links but if you are however looking to generate payment links in bulk for multiple products and varying amounts, Google Sheets can help.

Here’s a sample demo:

Razorpay Google Sheets

To get started, open your Razorpay dashboard, go to Settings > API Keys > Generate Key to generate the Key Id and Key Secret for your account.

Next, make a copy of the Razorpay sheet in your Google Drive. Go to Tools > Script Editor and replace the Key Id and Key Secret with the ones generated in the previous step. Then, click on the Run menu to authorize the script with your Google Account.

Switch to the Google Sheet and you can now use the custom Google Sheets function RAZORPAY() to generate dynamic payment links.

If you would like to generate payment links for multiple rows in the Google Sheet, just write the formula in the first row and drag the crosshairs to the other rows as show in the demo below. Array Formulas are not supported yet.

Razorpay Google Sheets

You can use Mail Merge with Gmail to request payments from your customers over email. If the column title is Payment Link in Google Sheets, simply put {{Payment Link}} in the email template and these will be replaced with the actual Razorpay payment links customized for each customer.

You may also use Document Studio to create PDF invoices and embed the payment links directly in the invoice. Please watch this video tutorial to learn more.

How Razorpay Works with Google Sheets

If you are curious to know how integration of Google Sheets and Razorpay works, the answer is Google Apps Script. The underlying code invokes the Razorpay API with your credentials and writes the generated payment links in the Google Sheet.

The custom Google Sheets function uses the built-in caching service of Apps Script to reduce latency and improve performance.

const RAZORPAY_KEY_ID = "<<Your Razorpay Key Id>>";
const RAZORPAY_KEY_SECRET = "<<Your Razorpay Key Secret>>";

/**
 * Generate payment links for Razorpay in Google Sheets
 *
 * @param {number} amount The amount to be paid using Razorpay
 * @param {string} currency The 3-letter currency code (optional)
 * @param {string} description A short description of the payment request (optional)
 * @return Razorpay Payment Link
 * @customfunction
 */

const RAZORPAY = (amount, currency, description) => {
  const payload = JSON.stringify({
    amount: amount * 100,
    currency,
    description,
  });

  // Use caching to improve performance
  const cachedLink = CacheService.getScriptCache().get(payload);

  if (cachedLink) return cachedLink;

  // Generate the Authorization header token
  const base64token = Utilities.base64Encode(
    `${RAZORPAY_KEY_ID}:${RAZORPAY_KEY_SECRET}`
  );

  // Invoke the Razorpay Payment Links API
  const response = UrlFetchApp.fetch(
    "https://api.razorpay.com/v1/payment_links/",
    {
      method: "POST",
      headers: {
        Authorization: `Basic ${base64token}`,
        "Content-Type": "application/json",
      },
      muteHttpExceptions: true,
      payload: payload,
    }
  );

  // The short_url contains the unique payment link
  const { short_url = "" } = JSON.parse(response);

  // Store the generated payment link in the cache for 6 hours
  CacheService.getScriptCache().put(payload, short_url, 21600);

  return short_url;
};

How to Share Files in Google Drive with Multiple Users

The Google Drive API makes it easy to share files and folders with other users programmatically with the help of Apps Script.

For instance, here’s a snippet of code that will let you share the file with another Google Account user and provide them edit access to the file. Replace the role from writer to reader to give them read-only access.

const shareFilesInGoogleDrive = (fileOrFolderId, emailAddress) => {
  Drive.Permissions.insert(
    {
      role: "writer", // or "reader" or "commenter"
      value: emailAddress,
      type: "user",
    },
    fileOrFolderId,
    {
      supportsAllDrives: true,
      sendNotificationEmails: true,
    }
  );
};

It is recommended that you set the sendNotifications flag to true as it will send an email notification when the file is shared with a user who may not have a Google account.

Share Files with Multiple Users

A limitation of the Drive API is that you can only share files with one user at a time. Google Apps Script is synchronous - it doesn’t support the async/await pattern of JavaScript Promises and you therefore cannot run the code in parallel.

There’s however a simple workaround to help you share a file or folder in Google Drive with multiple users in one go in parallel using the UrlFetchApp service.

const shareGoogleDriveFileWithMultipleUsers = () => {
  const fileId = "<Drive File Id>";
  const editors = ["angus@gmail.com", "kiran@school.edu", "jacob@corp.com"];

  const API = "https://www.googleapis.com/drive/v3/files";
  const queryString = "supportsAllDrives=true&sendNotifications=true";
  const accessToken = ScriptApp.getOAuthToken();

  const requests = editors.map((emailAddress) => ({
    url: `${API}/${fileId}/permissions?${queryString}`,
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${accessToken}`,
    },
    muteHttpExceptions: true,
    payload: JSON.stringify({
      role: "writer",
      type: "user",
      emailAddress: emailAddress,
    }),
  }));

  UrlFetchApp.fetchAll(requests);
};

In the snippet above, we are directly invoking the Google Drive API (v3) instead of the DriveApp service of App Script. The fetchAll allows you make multiple HTTP requests in a single request and returns an array of responses.

Please ensure that the following scopes are added in your appsscript.json file:

  {
    ...
    "oauthScopes": [
      "https://www.googleapis.com/auth/script.external_request",
      "https://www.googleapis.com/auth/drive",
    ],
   ...
  }

Useful npm Tips and Tricks that Developers Should Know

Node Package Manager, or npm, is a tool to install and manage JavaScript packages in your project. And if you have Node installed on your computer, you have already have npm as well.

NPM Tips and Tricks

npm Commands You Should Know

This is not a tutorial for learning npm, the official docs are good place to get started, but a collection of tips and tricks that will help you do more with the npm utility.

Let’s jump right into the list of useful commands:

Instantly run packages without installing

The NPM registry is a treasure trove for finding packages that do useful stuff and aren’t just for programmers.

For instance, the speed-test package shows the speed of your internet connection. The emoj packages helps you search for emojis from the terminal. And wifi-passwords is a simple way to know the password of your current WiFi network.

You can run these utility packages directly from the command line without installing them using the npx command.

npx speed-test
npx emoj unicorn
npx public-ip-cli
npx wifi-password-cli

Install npm packages faster

You’ve probably used npm install to install packages, and dependencies, in the local node_modules folder of a project. Replace this command with npm-ci and you’ll be able to install packages significantly faster.

npm ci

If a node_modules folder is already present, it will be automatically removed before npm ci begins to install packages.

Recover space

If you have been working with npm packages for some time, the various node_modules folders on the disks could be consuming several gigabytes of space. The very useful npkill finds all node_modules folders on your system and lets you delete them interactively.

npx npkill

Quickly download a Git repository

Most developers use the git clone command to download a Git repository. However, this also downloads the entire git history making the process slower. The degit package can download the latest commit to the master branch locally and you need not specify the full Github URL.

npx degit username/repo
npx degit labnol/apps-script-starter

List installed packages

Generate a list of all npm packages that are installed on the system with global scope. Remove the -g flag to list only packages installed in the current project directory.

npm ls --depth=0
npm ls -g

Find unused dependencies

The depcheck command will list all the npm packages that are not used in the project based on the dependencies in package.json.

npx depcheck

Use the command npm uninstall <package-name> to uninstall any unused package.

Find outdated dependencies

Get a list of all outdated packages in your current project. This command checks every single module listed in the package.json file and compares it with the latest version available in the NPM registry.

Add the -g flag to get all outdated packages that are installed globally on the system.

npm outdated
npm outdated -g

Update the package versions

The ncu command will update the package.json file with the latest version of the packages listed in the dependencies and devDependencies sections.

Or use the npm-check -u command to update packages to their latest version in interactive mode.

npm-check
npm-check -u
ncu -u

Remove extra packages

Use the prune command to remove all packages that are installed locally but not listed in the package.json file. If the —dry-run flag is used then no changes will actually be made.

npm prune

Alternatively, you can remove the node_modules folder and run npm ci again.

Find vulnerable packages

Run the audit command to check for vulnerabilities in the packages listed in the dependencies and devDependencies sections. Add the fix flag to automatically apply the fixes, if any.

npm audit
npm audit fix

Useful NPM Package Websites

  • bundlephobia.com - Upload your package.json file and get an idea of how much it would cost (size-wise) to install the dependencies.
  • diff.intrinsic.com - Compare any two versions of a npm package and know which files have changed in the update.
  • npmtrends.com - Compare the relative popularity of packages across the npm registry based on the number of downloads.

Essential Date Functions for Google Sheets

Google Sheets Date Functions

Dates are internally stored as sequential serial numbers in Google Sheets. This serial number represents the number of days elapsed since December 31, 1899.

You can use the DATEVALUE function to convert any date input to a number that represents the date. For instance, both the functions DATEVALUE("Jan 1") and DATEVALUE("01-Jan-2021") return the same number (44197) though the inputs have vastly different formats.

The function TODAY() returns the current date while the function NOW() returns the current date and time. Both these functions do not require any arguments and they update when any cell in the Google Sheet is changed.

The function NOW() + 2 returns the current date and time plus two days while NOW() - 9/24 returns the date and time 9 hours ago since 1 = 24 hours.

The functions YEAR(), MONTH() and DAY() can be used extract the year, month and day of the date that is passed as an argument.

The DAYS() function calculates the number of days between two dates. Internally, it calculates the DATEVALUE of the first date and the DATEVALUE of the second date and subtracts the two numbers.

If you want to calculate the number of months between two dates, you can use the DATEDIF() function with the third argument set to M. For instance, the function =DATEDIF("Jan 1, 1951", TODAY(), "M") returns the number of months between January 1951 and today.

The YEARFRAC() function calculates the number of years that has passed between two dates.

Tip: You may use these date functions in Google Sheets with Array Formulas to schedule emails with Gmail Mail Merge.

Use the EDATE() function to calculate a date that is a specified number of months before or after a specified date. For instance, EDATE(TODAY(), -1) returns the date that is one month before the current date.

The EOMONTH() function helps you calculate the last day of the given month. For instance, EOMONTH(TODAY(), -1) returns the last day of the previous month. Add 1 to the result, =EOMONTH(TODAY(),-1)+1, and you’ll get the first day of the current month.

The WEEKDAY() function returns the day of the week corresponding to a date with Sunday representing 1, the first day of the week. Set the second argument to 2 and days of the week will be numbered starting with Monday.

The WORKDAY() function calculates the date that is a specified number of days before or after a specified date, excluding weekends. For instance, WORKDAY(TODAY(), -7) returns the date that is 7 working days before the current date.

Likewise, the NETWORKDAYS() function calculates the number of working days between two dates provided as arguments. Combine this with EOMONTH to calculate the number of working days that are left till the end of the the current month =NETWORKDAYS(TODAY(), EOMONTH(TODAY(),0))

Google Sheets Date Formulas for Common Scenarios

TaskWorking Formula
Add number of days to a date=A1 + 5
Get a day that is 6 months prior to a date=EDATE(A1, -5)
Add number of years to a date=DATE(YEAR(A1) + 5, MONTH(A1), DAY(A1))
Difference in days between two dates=DAYS(A1, A2)
Total working days between two dates=NETWORKDAYS(A1, A2)
Get a date that is 10 working days from now=WORKDAY(TODAY(), 10)
Get the total number of months between two dates=DATEIF(A1, A2, "M")
Get the difference in years between two dates=DATEIF(A1, A2, "Y")
Get the number of days in the current month=EOMONTH(TODAY(), 0) - (EOMONTH(TODAY(), -1) + 1)
Print the day of the week=TEXT(TODAY(), "ddddd")
Calculate the age in years=ROUNDDOWN(YEARFRAC(A1, TODAY(), 1))
Days until your next birthday=DAYS(DATE(YEAR(A1)+DATEDIF(A1,TODAY(),"Y")+1, MONTH(A1),DAY(A1), TODAY())
Months and days between two dates=DATEDIF(A1,A2,"YM")&" months, "&DATEDIF(A1,A2,"MD")&" days"

You can copy this Google Sheet to get all the working formulas mentioned in this tutorial.

How to Convert HTML to PDF File with Google Script

With Google Apps Script, you can easily convert any HTML content into a PDF file. The converted PDF file can be either saved to a folder in your Google Drive, you can email the file as an attachment or the use the UrlFetchApp service of Apps Script to post the PDF file to an external service like Amazon S3 or Dropbox.

/* This function will convert HTML content to a PDF file, 
   and also send it as an email attachment */

const convertHTMLtoPDF = () => {
  const htmlContent = `
   <p>All standard HTML5 tags are supported during conversion
   including <b>bold</b>, <i>italic</i>, <u>underline</u>, tables,
   and <a href='https://digitalinspiration.com/'>inline URLs</a></p>`;

  const blob = Utilities.newBlob(htmlContent, MimeType.HTML);
  blob.setName("file.pdf");

  const recipientEmail = "amit@labnol.org";
  const emailSubject = "The PDF file is attached";

  MailApp.sendEmail({
    to: recipientEmail,
    subject: emailSubject,
    htmlBody: htmlContent,
    attachments: [blob.getAs(MimeType.PDF)],
  });
};

This approach is recommended since it doesn’t require access to any sensitive OAuth scopes and uses the Utilities services of Apps Script to create a Blob object from an HTML string.

Create PDF files with Google Drive

You can also use the Advanced Drive Service of Apps script to convert HTML content into PDF using a Google Document at an intermediate step.

The idea is that you create a Google Document in Drive with your HTML content and then export that document as a PDF file and trash the temporary document. Or you can override the content of the HTML document with the PDF blob.

To get started, go to your Apps Script editor, open the appsscript.json manifest file and update scope as shown below:

{
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Drive",
        "serviceId": "drive",
        "version": "v2"
      }
    ]
  },
  "oauthScopes": ["https://www.googleapis.com/auth/drive.file"],
  "runtimeVersion": "V8",
  "timeZone": "Asia/Kolkata",
  "exceptionLogging": "STACKDRIVER"
}

Next, inside the main code editor, paste the following snippet. It takes a three step approach:

  1. Convert the HTML string to a blob
  2. Convert the Blob into a Google Document
  3. Export the Google Document as a PDF file and trash the file created in step 2.
const convertHTMLtoPDF = () => {
  const htmlContent = `
   <p>All standard HTML5 tags are supported during conversion
   including <b>bold</b>, <i>italic</i>, <u>underline</u>, tables,
   and <a href="https://digitalinspiration.com/">inline URLs</a></p>`;

  const { id, exportLinks } = Drive.Files.insert(
    { mimeType: MimeType.GOOGLE_DOCS },
    htmlBlob: Utilities.newBlob(htmlContent, MimeType.HTML)
  );

  const pdfExportLink = exportLinks[MimeType.PDF];

  const blob = UrlFetchApp.fetch(pdfExportLink, {
    headers: { Authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
  }).getBlob();

  Drive.Files.trash(id);

  const { alternateLink } = Drive.Files.insert({ title: "file.pdf" }, blob);

  Logger.log("View file %s", alternateLink);
};

Tip: We are using the drive.file reduced scope in the manifest file but if you wish to save files in specific folders of your Google Drive, or Shared Team Drives, use the broader googleapis.com/auth/drive scope.

Convert HTML to PDF with Chrome Puppeteer

If you wish to build a standalone HTML to PDF conversion engine that doesn’t use any of the Google Drive services, Chrome Puppeteer with Node JS can be a good option. You can host the service on AWS Lambda or Google Cloud functions and invoke the service with an HTTP call.

const express = require("express");
const chromium = require("chrome-aws-lambda");

const app = express();

app.use(express.json());
app.use(express.urlencoded({ extended: false }));

const html2pdf = async (html) => {
  const browser = await chromium.puppeteer.launch({
    args: chromium.args,
    executablePath: await chromium.executablePath,
    headless: true,
    ignoreHTTPSErrors: true,
  });

  const page = await browser.newPage();

  await page.setContent(html, {
    waitUntil: ["networkidle0", "load", "domcontentloaded"],
    timeout: 30000,
  });

  const pdf = await page.pdf({
    format: "A4",
    printBackground: true,
  });

  await browser.close();

  return pdf;
};

app.post("/pdf", async (request, response) => {
  try {
    const { content } = request.body;
    const pdf = await html2pdf(content);
    response.contentType("application/pdf");
    response.status(200).send(pdf);
  } catch (f) {
    response.status(500).send(f.message);
  }
});

const PORT = process.env.PORT || 8080;

app.listen(PORT, async () => {
  console.log(`App listening on port ${PORT}`);
});

Sort by Random – How to Randomize the Order of Rows in Google Sheets

You have a workbook in Google Sheets that contains multiple rows of data and you are required to sort the list in a random order. For instance, your sheet may contain the names of your team members and you need to reshuffle the list before assigning tasks to each of the members randomly. Or your Google Sheet may have the email addresses of people who participated in a giveaway and you need to pick any three random entries in an unbiased manner for the prize.

There are multiple ways to randomize the data rows in Google Sheet. You can either use the built-in SORT function of Google Sheets or create a menu-based function that lets you randomize data with a click.

Demo - Make a copy of this Google Sheet to try random sort with your own data in sheets.

Sort Google Sheets in Random Order

125956

Open your Google Sheet that contains the list of data and create a new sheet. Paste the following formula in A1 cell of this empty sheet.

=SORT(Customers!A2:D50, RANDARRAY(ROWS(Customers!A2:A50), 1), FALSE)

The first argument of the SORT function specifies the range of data that needs to be sorted in A1 Notation, the second argument creates a virtual column of same dimension but filled with random numbers and third order specifies the sort order from smallest to largest.

You may also want to replace Customers in the formula with the exact name of your Google Sheet. If the sheet name contains spaces, enclose your sheet name in single as quotes like 'Employee List'!A2:D50. We start with row 2 since the first row is assumed to contain the header (titles).

The advantage with this approach is that it doesn’t alter the source of data as the randomized list of data appears in a new sheet.

Sort a List Randomly in Google Sheets with Apps Script

If you prefer a more automated approach that doesn’t require you to manually add formulas each time you need to perform a random sort, take the Apps Script route.

Sort Google Sheets Randomly

Open your Google Sheet, go to the Tools menu and choose Script editor. Copy-paste the following code in the editor and save. Reload the Google Sheet and you should see a new menu as shown in the screenshot above.

/** @OnlyCurrentDoc */

// Sort data in random order
const sortRowsInRandomOrder = () => {
  // Get the current sheet that contains the list of data
  const sheet = SpreadsheetApp.getActiveSheet();

  // Get the first non-empty column
  const column = sheet.getLastColumn() + 1;

  // Add the RAND() formula to all rows in the new column
  sheet
    .getRange(1, column)
    .setFormula("=RAND()")
    .autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

  // Sort the entire range of data using the random values
  // Do not include the first row of data (header) for sort
  sheet.getDataRange().offset(1, 0).sort({ column });

  // Remove the temporary column from Google sheet
  sheet.deleteColumn(column);

  // Flush the changes
  SpreadsheetApp.flush();
};

// Add the menu to Google Sheets
const onOpen = () => {
  SpreadsheetApp.getUi()
    .createMenu("Randomize Rows")
    .addItem("Start", "sortRowsInRandomOrder")
    .addToUi();
};

Keep Shuffling Rows

Go to the Randomize Rows menu and choose Start. It creates a temporary column, fill the RAND() formula in the new column for the entire range of cells, sorts the sheet range by this data and then remove the temporary column automatically.

You can click the same menu item multiple times and it will keep shuffling the rows in random order.