How to Get the Permanent URL of an Email Message in Gmail with Apps Script

All email messages in your Gmail inbox have a permanent web address and you can add this URL to your bookmarks to quickly access that message in the future. You can save these message links in your task list or your meeting notes as they provide important context to the conversation.

Gmail Email Link Bookmarks

The URL of any email message is Gmail follows a standard format:

https://mail.google.com/mail/u/<<UserId>>/#label/<<Label>>/<<UniqueId>>

The UserId is the sequential ID of the currently-logged Gmail account (default is 0). The Label is the name of the Gmail label that the message is in (or use all). The UniqueId is a unique ID that Gmail assigns to each message.

The key here is the UniqueId that is internally assigned by Gmail.

When you send an email with Google Apps Script, the Gmail API returns a unique ID that you can use to determine the URL of the email message in your sent items.

Here’s a simple procedure to send an email that is base64 encoded.

const sendGmailMessage = (mimeText) => {
  const GMAIL_API =
    "https://gmail.googleapis.com/upload/gmail/v1/users/me/messages/send";
  const params = {
    method: "POST",
    contentType: "message/rfc822",
    headers: {
      Authorization: `Bearer ${ScriptApp.getOAuthToken()}`,
    },
    payload: mimeText,
  };
  const response = UrlFetchApp.fetch(GMAIL_API, params);
  const { id: messageId } = JSON.parse(response.getContentText());
  return messageId;
};

Now that you have the messageId of the outgoing email message, there are at least three ways to get the URL (permalink) of the email message:

Option 1: Use the standard URL format

const getEmailMessageUrl = (messageId) => {
  return `https://mail.google.com/mail/u/0/#all/${messageId}`;
};

Option 2: Use Apps Script to get the email thread URL

In this approach, we get the associated thread of the email message and then get the URL of the first message in the thread.

const getThreadUrl = (messageId) => {
  const message = GmailApp.getMessageById(messageId);
  return message.getThread().getPermalink();
};

Option 3: Use the Message-Id in Email Header

This is my favorite approach because it is the most reliable. When you send an email message, a unique message ID is assigned to the email message by the sending service. This message ID is stored in the Message-Id header of the email message and is used by your email client to group messages in the same conversation.

Gmail provides a special rfc822msgid search operator to search emails by message ID and we can use this search operator to get the URL of the email message.

const getMessageUrl = (messageId) => {
  const message = GmailApp.getMessageById(messageId);
  const rfc822Id = message.getHeader("Message-Id");
  const searchQuery = `rfc822msgid:<${rfc822Id}>`;
  return `https://mail.google.com/mail/u/0/#search/${searchQuery}`;
};

Related: Get a second email address with your @gmail address

How to Import Lodash in your JavaScript Projects for Lowest Bundle Size

Lodash is an extremely popular JavaScript library that provides a lot of useful functions for working with strings, arrays and objects in your web projects.

Some of the Lodash functions are now supported natively in modern JavaScript, but the library still adds value and saves you time.

For instance, if you want to generate a random number between 1 and 10, the _.random(1, 10) function is a great way to do it, similar to the RANDBETWEEN function of Google Sheets. The _.shuffle() function can help you quickly shuffle an array of values.

The Correct Way to Include Lodash

If your JavaScript project requires Lodash, you can include the library in your code in 4 different ways.

1. Import the entire lodash library

import _ from "lodash";

const capitalizeFirstName = (name) => {
  const result = _.capitalize(name);
  console.log(response);
};

2. Import using named aliases

import { capitalize } from "lodash";

const capitalizeFirstName = (name) => {
  const result = capitalize(name);
  console.log(response);
};

3. Import specific methods by path

import capitalize from "lodash/capitalize";

const capitalizeFirstName = (name) => {
  const result = capitalize(name);
  console.log(response);
};

4. Use per-method lodash packages

import capitalize from "lodash.capitalize";

const capitalizeFirstName = (name) => {
  const result = capitalize(name);
  console.log(response);
};

Which importing method would result in the lowest bundle size?

The option #1 will include the entire lodash library in your output bundle and is not recommended. The second option will also import the full library and should be avoided.

The #4 method of importing per-method lodash packages will result in the lowest bundle size, but it is not recommended since this approach will be deprecated in the future versions of lodash.

The approach #3 is recommended since it will only import the specific Lodash methods you need and also reduce the bundle size.

Bonus Tip: Memoization with Lodash

The Lodash library includes a memoization method called _.memoize() which is useful for caching expensive functions.

import memoize from "lodoash/memoize";

const expensiveFunction = (input) => {
  return input * input;
};

const memoizedFunction = memoize(expensiveFunction);

console.log(memoizedFunction(5)); // Calculates the square of 5
console.log(memoizedFunction(5)); // Returns the cached value

There’s however a big limitation of memoization with Lodash - it will only use the first parameter of the function as the cache key and ignore the rest. Let me explain.

const add = (a, b) => {
  return a + b;
};

const memoizedAdd = _.memoize(add);
console.log(memoizedAdd(1, 2)); // Calculates the sum of 1 and 2 and caches the result
console.log(memoizedAdd(1, 3)); // Returns the cached value which is 3 (incorrect)

As you may have noticed, the second parameter of the function is ignored and thus the result is incorrect since it returned the cached value based on the first parameter itself.

Memoization with Multiple Parameters

To fix this problem, you can use an alternative memoization library like fast-memoize or you can add a resolver function to the memoization method as shown below.

const multiply = (a, b) => {
  return a * b;
};

const resolver = (...args) => {
  return JSON.stringify(args);
};

const memoizedMultiply = _.memoize(multiply, resolver);

console.log(memoizedMultiply(1, 2)); // Calculates the product of 1 and 2 and caches the result
console.log(memoizedMultiply(1, 3)); // Calculates the product of 1 and 3 and caches the result
console.log(memoizedMultiply(1, 2)); // Returns the cached value

Improve Performance of Google Apps Script with Memoization

A folder in Google Drive contains a bunch of CSV files and you are required to write a Google Script to find a particular value in the CSV files. The solution is simple:

  1. Use the Drive API to get a list of CSV files in the specified folder.
  2. Parse the CSV files one by one using the Utilities.parseCsv() function.
  3. Read the CSV file, line by line, until the value is found and return the line number.
const findContentInCSVFiles = (folderId, searchString) => {
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType("text/csv");

  while (files.hasNext()) {
    const file = files.next();
    const fileContent = file.getBlob().getDataAsString();
    const linesOfData = Utilities.parseCsv(fileContent, ",");

    let found = false;
    let lineNumber = 0;

    for (; lineNumber < linesOfData.length && !found; lineNumber += 1) {
      const line = linesOfData[lineNumber];
      found = line.find((element) => element === searchString);
    }

    if (found) {
      return `${searchString} found in line #${
        lineNumber + 1
      } of file ${file.getName()}`;
    }
  }
  return "String not found :(";
};

Optimize Google Script Performance

The code to read CSV files and find the required value is simple but not efficient. You’ve to perform the same expensive operation for every value that you have to search in the folder of CSV files.

Memoization is a simple optimization technique that can be used to improve the performance of your Google Apps Script code. The basic idea is that you cache the results of an expensive function call using closures. If the function is called again with the same arguments, the cached result is returned instead of calling and executing the function all over again.

const memoize = (func) => {
  // Cache for storing the previously computed results
  const cache = {};
  return (...args) => {
    // Serializer to convert N arguments to a string
    const key = JSON.stringify(args);
    if (typeof cache[key] === "undefined") {
      cache[key] = func(...args);
    }
    return cache[key];
  };
};

const memoizedFindFunction = memoize(findContentInCSVFiles);

const findContentInFiles = () => {
  const FOLDER_ID = "<<folder id>>";
  const SEARCH_STRING = "hello world!";
  const response = memoizedFindFunction(FOLDER_ID, SEARCH_STRING);
  Logger.log(resonse);
};

The memoization function is called with the arguments of the original function. The result of the function is stored in a cache and returned when the same arguments are passed again.

How to Change the Date Format in Google Sheets

Dates in Google Sheets are internally stored as numbers and the value is equal to the number of days since 30th December 1899, midnight. The time values are stored as a fractional number.

For instance, if the date in a cell is Jan 1 1990, Google Sheet will store the cell value as 2. If the date has a time component, say Jan 1 1900 6 PM, the internal date value will be 2.75.

The date and time values in Google Sheets are commonly displayed in the dd/mm/yyyy format, depending on your Spreadsheet locale, but this display format can be easily customized using the built-in TEXT function.

For instance, a date like 15/10/2021 can be displayed as Oct 15 2021 or in a long format like Friday, October 15 2021 or you may extract the time component and display it as 03:52 PM.

Convert date Formats in Google Sheets

Convert Date Formats in Google Sheets

The TEXT function of Google Sheets allows to convert the date and time values in a sheet to a different format. It takes two parameters:

  1. The date or time value to be converted.
  2. The preferred format to convert the date or time value to.
=TEXT(A1, "MMMM d, YYYY")

Here are some sample date formats that you can use in the second parameter of the TEXT function:

Date and Time PatternResult
MMMM d, YYYYOctober 21, 2021
dd-MMM-YYYY08-Dec-2021
MMM d, YYYYDec 3, 2021
dd, MMMM DD YYYYYTue, October 19 2021
dddTuesday
d/MM/YYYY30/11/2021
dd MMM YYYY06 Feb 2022
mmm-yyOct-21
dd/mm/yy h:mm22/10/21 22:31
hh:mm:ss am/pm01:11:39 PM
h:mm14:23
h:mm am/pm9:58 PM
MMM-dd h:mm am/pmOct-20 10:37 PM
MMM DD, ‘YY h:mm am/pmOct 31, ‘21 10:34 AM

You can view the complete list in this Google Sheet.

Repeated Pattern in Custom Date Formats

The placeholders (like d, m or y) have different meanings depending on the number of pattern letters.

For instance, if the input date is October 5, the format code d will display the day of the month as 5 but if the format code is dd it will display zero-padded value as 05. If the format code is ddd, the result is an abbreviated day of the week Tue but if the format code is dddd, the full day of the week as Tuesday gets displayed.

Similarly, for the month placeholder, mm will display the zero-padded numerical value but mmm and mmmm will display the abbreviated and full month name respectively.

Date Formats with Array Formulas

If you have a date column in Google Sheets and you want to display the date in a different format, you can use an array formula in a new column to convert the dates.

Assuming that the date column is in cell A1, you can use the following array formula in the first cell of an empty column to display the same date and time value but in a different format.

=ARRAYFORMULA(
  IF(ROW(A:A)=1,"New Date Format",
  IF(ISBLANK(A:A),"",TEXT(A:A, "MMMM dd, YYYY"))))

This can be very handy for Google Sheets that are storing Google Form responses. Google Sheet will always show the response timestamp in your locale but you can add a new column to display the date and time in a different format.

Also see: Google Sheets Formulas for Google Forms

Date Conversion

Make all Shapes the Same Size in Google Slides

Microsoft PowerPoint has this really useful feature that lets you to easily resize multiple shapes in a slide to the same size. You can select the shapes you want to resize and then click on the Format Pane button. Here, under the Size and Position tab, you can resize the shapes to the required size.

Resizes Shapes in Microsoft PowerPoint

Google Slides doesn’t allow you to resize multiple shapes in a slide but you can use Google Apps Script to do the same thing. Go to the Tools menu and select Script Editor. Here copy-paste the code below and click on the Run button.

It will match the height and width of the first shape in the slide and resize all the shapes in the slide to the same height and width. The shapes are also reposition such that there’s equal distance between the shapes and the top edge of the shapes are in alignment.

const resizeSlideShapes = () => {
  const SPACING = 20;
  const [slide] = SlidesApp.getActivePresentation().getSlides();
  const [baseShape, ...targetShapes] = slide.getShapes();

  // Is the shape rectangular or triangular
  const shapeType = baseShape.getShapeType();

  // Get the shape height and width
  const height = baseShape.getHeight();
  const width = baseShape.getWidth();

  // Get the co-ordinates of the base shape
  const topPosition = baseShape.getTop();
  const leftPosition = baseShape.getLeft();

  targetShapes
    .filter((shape) => shape.getShapeType() === shapeType)
    .forEach((shape, index) => {
      shape.setHeight(height);
      shape.setWidth(width);
      shape.setTop(topPosition);
      shape.setLeft(leftPosition + (width + SPACING) * (index + 1));
    });
};

The Google Script can handle both Rectangle and Triangle shapes. Please note that the first shape in the slide is the base shape and will determine the height and width of the other shapes in the same slide.

Match Rectangle Shapes

Match Shape Sizes

Match Triangle Shapes

Resize and Align Shapes

You can use the same technique to recolor shapes and make all shapes the same color. You can play around with the shapes template here.

Improve Performance of Google Apps Script with Memoization

A folder in Google Drive contains a bunch of CSV files and you are required to write a Google Script to find a particular value in the CSV files. The solution is simple:

  1. Use the Drive API to get a list of CSV files in the specified folder.
  2. Parse the CSV files one by one using the Utilities.parseCsv() function.
  3. Read the CSV file, line by line, until the value is found and return the line number.
const findContentInCSVFiles = (folderId, searchString) => {
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType("text/csv");

  while (files.hasNext()) {
    const file = files.next();
    const fileContent = file.getBlob().getDataAsString();
    const linesOfData = Utilities.parseCsv(fileContent, ",");

    let found = false;
    let lineNumber = 0;

    for (; lineNumber < linesOfData.length && !found; lineNumber += 1) {
      const line = linesOfData[lineNumber];
      found = line.find((element) => element === searchString);
    }

    if (found) {
      return `${searchString} found in line #${
        lineNumber + 1
      } of file ${file.getName()}`;
    }
  }
  return "String not found :(";
};

Optimize Google Script Performance

The code to read CSV files and find the required value is simple but not efficient. You’ve to perform the same expensive operation for every value that you have to search in the folder of CSV files.

Memoization is a simple optimization technique that can be used to improve the performance of your Google Apps Script code. The basic idea is that you cache the results of an expensive function call using closures. If the function is called again with the same arguments, the cached result is returned instead of calling and executing the function all over again.

const memoize = (func) => {
  // Cache for storing the previously computed results
  const cache = {};
  return (...args) => {
    // Serializer to convert N arguments to a string
    const key = JSON.stringify(args);
    if (typeof cache[key] === "undefined") {
      cache[key] = func(...args);
    }
    return cache[key];
  };
};

const memoizedFindFunction = memoize(findContentInCSVFiles);

const findContentInFiles = () => {
  const FOLDER_ID = "<<folder id>>";
  const SEARCH_STRING = "hello world!";
  const response = memoizedFindFunction(FOLDER_ID, SEARCH_STRING);
  Logger.log(resonse);
};

The memoization function is called with the arguments of the original function. The result of the function is stored in a cache and returned when the same arguments are passed again.

Find Product Prices in Google Sheets with Vlookup and Match Functions

You run a coffee shop and you are looking for a spreadsheet formula to quickly look up prices of the product that your customer has ordered. You have the price matrix stored in a Google Sheet with the names of beverages in one column and the quantity-wise prices in the adjacent columns.

When a customer selects their favorite beverage and the cup size, you can use the MATCH function to find the relative position of the column and row in the price table that matches the selected beverage and quantity. Next, use the INDEX function to find the actual price of the beverage in the selected quantity.

MATCH function in Google Sheets Price Table

In our Starbuck Coffee example, the coffee prices are stored in the range B2:B11. The customer’s beverage name (Caffè Mocha in this example) is stored in the cell G3. The following MATCH function will return the relative position of the selected beverage from the list of beverages.

=MATCH(G3, $B$2:$B$11, 0)

The third parameter of the MATCH function is set to 0 since we want the exact match and our price list is not sorted.

Similarly, the next MATCH function will return the relative position of the column that contains the price of the beverage based on the selected quantity. The cup sizes are stored in the range C2:E2. The selected cup size is stored in the cell H3.

=MATCH(H3, $B$2:$E$2, 0)

Now that we know the relative row and column position of the price value we are looking for, we can use the INDEX function to find the actual price from the table.

=INDEX($B$2:$E$11, H5, H7)

Use Vlookup with ArrayFormula and Match

For the next example, we have a customer order that contains multiple beverages, one per row. We want to find the price of each beverage and the total price of the order. Array Formulas will be a perfect fit here since we want to extend the same formula to all rows of the spreadsheet.

However, we’ll have to revisit our approach since the INDEX function used in the previous example cannot be used with Array Formulas as it cannot return multiple values. We’ll replace INDEX with a similar VLOOKUP function and combine it with the MATCH function to perform a two-way lookup (find the beverage by name and then look for the specific cup size).

The VLOOKUP function syntax, in simple English, is:

=VLOOKUP(
  What you want to look for (beverage name),
  Where you want to look for it (price table range),
  The column number containing the matching value (chosen cup size),
  Return an approximate or exact match (True or False)
)

The function will look for the beverage name in the specified price range (B2:E11) and, from the matching row, return the value of the cell in the column that corresponds to selected cup size.

The price range is not sorted so we will put FALSE for the fourth parameter.

The MATCH function will return the relative position of the column that contains the price of the selected quantity of the matching beverage:

=MATCH(
  What are you looking for (cup size),
  Where are you looking for it (cup size header range),
  0 if you want to find the exact value (default is 1)
)

If a row doesn’t contain the beverage name, the formula will return #N/A and thus we wrap the value in IFNA to prevent the formula from returning any errors.

Our final formula will thus look like:

=ARRAYFORMULA(IFNA(VLOOKUP(B14:B, $B$2:$E$11, MATCH(C14:C, $B$2:$E$2, 0), FALSE)))

VLOOKUP MATCH function

Download the Excel file - Price Lookup Sheet

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();
};