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

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.