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

from Digital Inspiration https://ift.tt/3phRXjw

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.

Add Hyperlinks in Google Docs

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

Change Text of Hyperlinks in Google Docs

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

Change Text and Hyperlinks in Google Docs

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

from Digital Inspiration https://ift.tt/3wQPe2R

Google Documents - How to Replace Text in Header and Footer

The upcoming release of Document Studio includes support for adding markers in the header, footer and the footnotes section of your Microsoft Word template. The add-on will automatically replace this placeholder text with actual values sourced from Google Sheets or Google Forms.

Replace Header and Footer with Document API

This Apps Script snippet uses the Google Docs API to find and replace multiple blocks of text in the header and footer section of your Google Document. The header and footer sections are children of the parent DOCUMENT section.

const replaceHeaderFooter = () => {
  // Returns the document with the specified ID
  const doc = DocumentApp.openById("DOCUMENT ID");

  // Retrieves the headers's container element which is DOCUMENT
  const parent = doc.getHeader().getParent();

  for (let i = 0; i < parent.getNumChildren(); i += 1) {
    // Retrieves the child element at the specified child index
    const child = parent.getChild(i);

    // Determine the exact type of a given child element
    const childType = child.getType();

    if (childType === DocumentApp.ElementType.HEADER_SECTION) {
      // Replaces all occurrences of a given text in regex pattern
      child.asHeaderSection().replaceText("", "Digital Inspiration");
    } else if (childType === DocumentApp.ElementType.FOOTER_SECTION) {
      // Replaces all occurrences of a given text in regex pattern
      child.asFooterSection().replaceText("", "© Amit Agarwal");
    }
  }

  // Saves the current Document.
  // Causes pending updates to be flushed and applied.
  doc.saveAndClose();
};

If the current document doesn’t include an header section, the getHeader() function will return null so you may wish to include additional checks to determine whether a document has an header or footer.

from Digital Inspiration https://ift.tt/2TmjlRh

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.

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.

from Digital Inspiration https://ift.tt/3wRcCgT