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.

Exceeded maximum execution time Exception in Google Apps Script

Google Apps Script is a serverless environment that makes it easy for you to work with Gmail, Google Drive and other services that are part of the Google Workspace platform.

When you run any code inside the Google Apps Script IDE, in simple English, it spins up a new server with the required environment that are necessary to run your application. This server is allotted a hard timeout limit and the App Script environment will halt the execution of the function if it exceeds the maximum execution time.

Exceeded maximum execution time

The maximum execution time varies based on the type of your Google Account. If you are running your Apps Script code inside a Gmail account, your functions can run for 6 minutes before it will be terminated. For Google Workspace accounts, because you are paying a monthly fee to Google per user, the timeout limit is more generous at 30 minutes.

If your Apps Script function / trigger exceeds the maximum timeout limit, the script will throw an exception like Exceeded maximum execution time or equivalent based on your script’s locale.

Exceeded maximum execution time
Se ha superado el tiempo máximo de ejecución.
Timpul maxim de executare a fost depășit
تجاوز الحد الأقصى لعدد مرات التنفيذ
Vượt quá thời gian thực thi tối đa
Durée d'exécution autorisée dépassée
Przekroczono maksymalny czas wykonywania
Limite massimo del tempo di esecuzione superato
เวลาประมวลผลเกินขีดจำกัดสูงสุด
Melebihi jumlah eksekusi maksimum
Превышено максимально допустимое время выполнения
Lumagpas sa maximum na oras ng execution

Avoid Maximum Execution Time Limit

You can include a simple time check in your Apps Script function, that it likely to take more than a few minutes to execute, and gracefully pause the request if is seen to be exceeding the time limit.

For instance, the Download Gmail add-on saves email messages from Gmail to Google Drive as PDF files. It grabs a bunch of messages from the Inbox, converts them to PDF and runs in a loop. If the execution is taking longer, it breaks from the loop automatically.

const GMAIL_USER = /(gmail|googlemail)/.test(
  Session.getActiveUser().getEmail()
);
const ONE_SECOND = 1000;
const ONE_MINUTE = ONE_SECOND * 60;
const MAX_EXECUTION_TIME = ONE_MINUTE * (GMAIL_USER ? 6 : 30);
const NOW = Date.now();

const isTimeLeft = () => {
  return MAX_EXECUTION_TIME > Date.now() - NOW;
};

const thisFunctionTakesTimeToExecution = () => {
  const threads = GmailApp.getInboxThreads(0, 100);
  for (let t = 0; t < threads.length && isTimeLeft(); t += 1) {
    // Save email to Google Drive
    Logger.log("Saving email...");
  }
};

How to Replace Accented Characters (diacritics) with English letters in Google Sheets

The REMOVE_ACCENTED function for Google Sheets will replace all accented characters in the referenced cell, like the letters è, õ, ā, ĝ and so on with their normal Latin equivalents.

Foreign accented characters in Google Sheets

To get started, make a copy of the Google Sheet, go to the Tools menu, choose Script Editor and copy the entire code to your clipboard.

Now open your own Google Sheet and paste the same code inside the Script editor of your sheet. Save and you should be able to use the REMOVE_ACCENTED function in your own sheets.

Input StringOutput string
A História de Malú e João MiguelA Historia de Malu e Joao Miguel
Símbolo de su unidad y permanenciaSimbolo de su unidad y permanencia
Tomás Gutiérrez AleaTomas Gutierrez Alea
Miguel Ángel Félix GallardoMiguel Angel Felix Gallardo

Internally, this function uses the deburr function of the popular lodash library that converts Latin-1 Supplement and Latin Extended-A letters to basic Latin letters and also removes any combining diacritical marks.

Find and Replace Accented Letters in Spreadsheets

const latinRegEx = /[\xc0-\xd6\xd8-\xf6\xf8-\xff\u0100-\u017f]/g;
const comboRegEx = `[\\u0300-\\u036f\\ufe20-\\ufe2f\\u20d0-\\u20ff]`;

/** Used to map Latin Unicode letters to basic Latin letters. */
const latinUnicodeLetters = {
  // Latin-1 Supplement block.
  '\xc0': 'A', '\xc1': 'A', '\xc2': 'A', '\xc3': 'A', '\xc4': 'A', '\xc5': 'A',
  '\xe0': 'a', '\xe1': 'a', '\xe2': 'a', '\xe3': 'a', '\xe4': 'a', '\xe5': 'a',
  '\xc7': 'C', '\xe7': 'c',
  '\xd0': 'D', '\xf0': 'd',
  '\xc8': 'E', '\xc9': 'E', '\xca': 'E', '\xcb': 'E',
  '\xe8': 'e', '\xe9': 'e', '\xea': 'e', '\xeb': 'e',
  '\xcc': 'I', '\xcd': 'I', '\xce': 'I', '\xcf': 'I',
  '\xec': 'i', '\xed': 'i', '\xee': 'i', '\xef': 'i',
  '\xd1': 'N', '\xf1': 'n',
  '\xd2': 'O', '\xd3': 'O', '\xd4': 'O', '\xd5': 'O', '\xd6': 'O', '\xd8': 'O',
  '\xf2': 'o', '\xf3': 'o', '\xf4': 'o', '\xf5': 'o', '\xf6': 'o', '\xf8': 'o',
  '\xd9': 'U', '\xda': 'U', '\xdb': 'U', '\xdc': 'U',
  '\xf9': 'u', '\xfa': 'u', '\xfb': 'u', '\xfc': 'u',
  '\xdd': 'Y', '\xfd': 'y', '\xff': 'y',
  '\xc6': 'Ae', '\xe6': 'ae',
  '\xde': 'Th', '\xfe': 'th',
  '\xdf': 'ss',
  // Latin Extended-A block.
  '\u0100': 'A', '\u0102': 'A', '\u0104': 'A',
  '\u0101': 'a', '\u0103': 'a', '\u0105': 'a',
  '\u0106': 'C', '\u0108': 'C', '\u010a': 'C', '\u010c': 'C',
  '\u0107': 'c', '\u0109': 'c', '\u010b': 'c', '\u010d': 'c',
  '\u010e': 'D', '\u0110': 'D', '\u010f': 'd', '\u0111': 'd',
  '\u0112': 'E', '\u0114': 'E', '\u0116': 'E', '\u0118': 'E', '\u011a': 'E',
  '\u0113': 'e', '\u0115': 'e', '\u0117': 'e', '\u0119': 'e', '\u011b': 'e',
  '\u011c': 'G', '\u011e': 'G', '\u0120': 'G', '\u0122': 'G',
  '\u011d': 'g', '\u011f': 'g', '\u0121': 'g', '\u0123': 'g',
  '\u0124': 'H', '\u0126': 'H', '\u0125': 'h', '\u0127': 'h',
  '\u0128': 'I', '\u012a': 'I', '\u012c': 'I', '\u012e': 'I', '\u0130': 'I',
  '\u0129': 'i', '\u012b': 'i', '\u012d': 'i', '\u012f': 'i', '\u0131': 'i',
  '\u0134': 'J', '\u0135': 'j',
  '\u0136': 'K', '\u0137': 'k', '\u0138': 'k',
  '\u0139': 'L', '\u013b': 'L', '\u013d': 'L', '\u013f': 'L', '\u0141': 'L',
  '\u013a': 'l', '\u013c': 'l', '\u013e': 'l', '\u0140': 'l', '\u0142': 'l',
  '\u0143': 'N', '\u0145': 'N', '\u0147': 'N', '\u014a': 'N',
  '\u0144': 'n', '\u0146': 'n', '\u0148': 'n', '\u014b': 'n',
  '\u014c': 'O', '\u014e': 'O', '\u0150': 'O',
  '\u014d': 'o', '\u014f': 'o', '\u0151': 'o',
  '\u0154': 'R', '\u0156': 'R', '\u0158': 'R',
  '\u0155': 'r', '\u0157': 'r', '\u0159': 'r',
  '\u015a': 'S', '\u015c': 'S', '\u015e': 'S', '\u0160': 'S',
  '\u015b': 's', '\u015d': 's', '\u015f': 's', '\u0161': 's',
  '\u0162': 'T', '\u0164': 'T', '\u0166': 'T',
  '\u0163': 't', '\u0165': 't', '\u0167': 't',
  '\u0168': 'U', '\u016a': 'U', '\u016c': 'U', '\u016e': 'U', '\u0170': 'U', '\u0172': 'U',
  '\u0169': 'u', '\u016b': 'u', '\u016d': 'u', '\u016f': 'u', '\u0171': 'u', '\u0173': 'u',
  '\u0174': 'W', '\u0175': 'w',
  '\u0176': 'Y', '\u0177': 'y', '\u0178': 'Y',
  '\u0179': 'Z', '\u017b': 'Z', '\u017d': 'Z',
  '\u017a': 'z', '\u017c': 'z', '\u017e': 'z',
  '\u0132': 'IJ', '\u0133': 'ij',
  '\u0152': 'Oe', '\u0153': 'oe',
  '\u0149': "'n", '\u017f': 's'
};

const basePropertyOf = (object) => (key) => object[key];
const characterMap = basePropertyOf(latinUnicodeLetters);

/**
 * Replace accented characters in Google Sheets with English letters.
 *
 * @param {string} input The input string with accented characters.
 * @return The input without accented characters.
 * @customfunction
 */
function REPLACE_ACCENTED(input) {
  if (input && typeof input === "string") {
    return input.replace(latinRegEx, characterMap).replace(comboRegEx, "");
  }
  return input;
}

How to Change the Font in your Google Documents with Apps Script

An organization recently migrated their Word Documents from Microsoft Office to Google Drive. The migration has been smooth but the Word documents imported as Google Docs are using Calibri, the default font family of Microsoft Word.

The company is looking to replace the fonts in multiple Google Documents such that the document headings using Georgia while the body paragraphs are rendered in Droid Sans at 12 pt.

Replace Font Styles in Google Docs

This example show how to replace the font family of your Google Documents of specific sections - the heading titles are rendered in a different font while the tables, list items, body and table of contents are formatted with a separate font.

const updateFontFamily = () => {
  const document = DocumentApp.getActiveDocument();

  const headingStyles = {
    [DocumentApp.Attribute.FONT_FAMILY]: "Georgia",
    [DocumentApp.Attribute.FONT_SIZE]: 14,
  };

  const normalParagraphStyles = {
    [DocumentApp.Attribute.FONT_FAMILY]: "Droid Sans",
    [DocumentApp.Attribute.FONT_SIZE]: 12,
  };

  const body = document.getBody();

  [...Array(body.getNumChildren())].map((_, index) => {
    const child = body.getChild(index);
    const childType = child.getType();
    if (childType === DocumentApp.ElementType.PARAGRAPH) {
      if (
        child.asParagraph().getHeading() === DocumentApp.ParagraphHeading.NORMAL
      ) {
        child.setAttributes(normalParagraphStyles);
      } else {
        child.setAttributes(headingStyles);
      }
    } else if (childType === DocumentApp.ElementType.TABLE) {
      child.setAttributes(normalParagraphStyles);
    } else if (childType === DocumentApp.ElementType.TABLE_OF_CONTENTS) {
      child.setAttributes(normalParagraphStyles);
    } else if (childType === DocumentApp.ElementType.LIST_ITEM) {
      child.setAttributes(normalParagraphStyles);
    }
  });

  document.saveAndClose();
};

How to Replace Text and Hyperlinks in Google Documents with Apps Script

The company’s handbook is written in Google Docs. The document spans several pages and now the writer has been asked to create links such that all mentions of the company name in the document are linking to the company’s official website.

It can be a time consuming task but with Google Apps Script, specific words in a document can be hyperlinked in bulk in one click.

This example show how to search and replace all occurrences of a text phrase, the company name in this case, and add links to a specific website.

const addLinks = () => {
  const searchPhrase = "Digital Inspiration";
  const hyperlink = "https://digitalinspiration.com/";

  const document = DocumentApp.getActiveDocument();
  const body = document.getBody();
  let search = null;

  while ((search = body.findText(searchPhrase, search))) {
    const searchElement = search.getElement();
    const startIndex = search.getStartOffset();
    const endIndex = search.getEndOffsetInclusive();
    searchElement.asText().setLinkUrl(startIndex, endIndex, hyperlink);
  }

  document.saveAndClose();
};

For the next iteration of the handbook, the company’s name has changed but the website domain is the same. The writer is required to change every instance of the company’s name in the document but the underlying hyperlink should not be modified..

const changeText = () => {
  const searchText = "Blue Widgets Inc.";
  const replaceText = "Orange Inc.";

  const document = DocumentApp.getActiveDocument();
  const body = document.getBody();
  let search = null;

  while ((search = body.findText(searchText, search))) {
    const searchElement = search.getElement();
    const startIndex = search.getStartOffset();
    const endIndex = search.getEndOffsetInclusive();

    const textElement = searchElement.asText();
    const existingLink = textElement.getLinkUrl(startIndex);
    textElement.deleteText(startIndex, endIndex);
    textElement.insertText(startIndex, replaceText);
    textElement.setLinkUrl(
      startIndex,
      startIndex + replaceText.length - 1,
      existingLink
    );
  }

  document.saveAndClose();
};

The next Apps Script snippets shows how to change all instance of the company name and also replace the site URL with another domain name.

const changeTextWithUrl = () => {
  const searchText = "Blue Widgets Inc.";
  const replaceText = "Orange Inc.";
  const replaceUrl = "https://digitalinspiration.com/";

  const document = DocumentApp.getActiveDocument();
  const body = document.getBody();
  let search = null;

  while ((search = body.findText(searchText, search))) {
    const searchElement = search.getElement();
    const startIndex = search.getStartOffset();
    const endIndex = search.getEndOffsetInclusive();

    const textElement = searchElement.asText();
    textElement.deleteText(startIndex, endIndex);
    textElement.insertText(startIndex, replaceText);
    textElement.setLinkUrl(
      startIndex,
      startIndex + replaceText.length - 1,
      replaceUrl
    );
  }

  document.saveAndClose();
};

How to Convert Column Numbers (e.g. 28) to A1 Notation (e.g. AB) in Google Sheets

Google Sheets includes built-in functions for converting cell references in A1 notation to row and column numbers and another function for converting column alphabets (like AA) into the column index (26 in this case).

=ADDRESS(23, 28, 4) - Returns the A1 style notation of the cell whose row number is 23 and column number is 28.

=COLUMN(C9) - Returns the column number of a specified cell C9 where column A corresponds to 1 and column AA corresponds to 27.

Column Numbers in A1 Notation

Get A1 Notation with JavaScript

If you are working with the Google Sheets API, you may sometimes needs to calculate the A1 notation style reference of a cell whose row and column numbers are known in the JSON data of the sheet.

For container bound Google Sheets, the getA1Notation() method can return the range address in A1 Notation.

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange(1, 2);
Logger.log(range.getA1Notation());

If you are not using the Spreadsheet service, you can also compute the A1 notation reference of a cell using simple JavaScript.

/**
 *
 * @param {number} row - The row number of the cell reference. Row 1 is row number 0.
 * @param {number} column - The column number of the cell reference. A is column number 0.
 * @returns {string} Returns a cell reference as a string using A1 Notation
 *
 * @example
 *
 *   getA1Notation(2, 4) returns "E3"
 *   getA1Notation(2, 4) returns "E3"
 *
 */
const getA1Notation = (row, column) => {
  const a1Notation = [`${row + 1}`];
  const totalAlphabets = "Z".charCodeAt() - "A".charCodeAt() + 1;
  let block = column;
  while (block >= 0) {
    a1Notation.unshift(
      String.fromCharCode((block % totalAlphabets) + "A".charCodeAt())
    );
    block = Math.floor(block / totalAlphabets) - 1;
  }
  return a1Notation.join("");
};

This is equivalent to =ADDRESS() function of Google Sheets.

Get Column Number from A1 Notation

The next function takes the cell reference in A1 notation and returns the column number and row number of any cell in the spreadsheet.

/**
 *
 * @param {string} cell -  The cell address in A1 notation
 * @returns {object} The row number and column number of the cell (0-based)
 *
 * @example
 *
 *   fromA1Notation("A2") returns {row: 1, column: 3}
 *
 */

const fromA1Notation = (cell) => {
  const [, columnName, row] = cell.toUpperCase().match(/([A-Z]+)([0-9]+)/);
  const characters = "Z".charCodeAt() - "A".charCodeAt() + 1;

  let column = 0;
  columnName.split("").forEach((char) => {
    column *= characters;
    column += char.charCodeAt() - "A".charCodeAt() + 1;
  });

  return { row, column };
};

This is equivalent to the =ROW() and =COLUMN() functions available in Google Sheets.