DocsServiceApp

<a name="top"></a>

<a name="overview"></a>

Overview

This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.

<a name="description"></a>

Description

The Google services, which are Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API, are growing now. But, unfortunately, there are still the processes that they cannot done. I created this GAS library for supporting the Google services.

The basic method of DocsServiceApp is to directly create and edit the data of Microsoft Docs (Word, Excel and Powerpoint). The created data of Microsoft Docs can be reflected to Google Docs (Document, Spreadsheet and Slides) by converting and copying the values. By this, the processes which cannot be achieved by Google services are achieved. So it can be considered that this DocsServiceApp is used as the wrapper for supporting Google service. I believe that this method will be able to be also applied for various scenes as the methodology. So I would like to grow this library.

Feature

For Google Docs

Google Document

• Retrieve table width and column width from the table. The tables inserted with the default width are included.

Google Spreadsheet

• Retrieve all images in Google Spreadsheet as an object including the cell range and image blob.
• Retrieve all comments in Google Spreadsheet as an object including the cell range and comments.
• Insert images in cells of Google Spreadsheet using the image blob.
• Create new Google Spreadsheet by setting the custom header and footer.
• Retrieve cell coordinates of cells with the quote prefix.
• Retrieve named functions.

Google Slides

• Create new Google Slides by setting the page size.

For Microsoft Docs

In the current stage, there are not methods for directly parsing Microsoft Docs files. This library can achieve this.

Microsoft Word

• Retrieve table width and column width.

Microsoft Excel

• Retrieve all values and formulas of the cells.
• Retrieve all sheet names.
• Retrieve all images as an object including the cell range and image blob.
• Retrieve all comments as an object including the cell range and comments.

Microsoft Powerpoint

There are no methods yet.

Library's project key

108j6x_ZX544wEhGkgddFYM6Ie09edDqXaFwnW3RVFQCLHw_mEueqUHTW

How to install

Install this library

• Open Script Editor. Click as follows:
• -> Resource
• -> Library
• -> Input the Script ID in the text box. The Script ID is 108j6x_ZX544wEhGkgddFYM6Ie09edDqXaFwnW3RVFQCLHw_mEueqUHTW.
• -> Add library
• -> Please select the latest version
• -> Developer mode ON (Or select others if you don't want to use the latest version)
• -> The identifier is "DocsServiceApp". This is set under the default.

You can read more about libraries in Apps Script here.

Please use this library with enabling V8 runtime.

About Google APIs

This library uses the following Google APIs. So when you want to use the library, please enable the following APIs at Advanced Google services. Ref

• Drive API: This is used for all methods.
• Sheets API: This is used for Google Spreadsheet.

About scopes

This library uses the following scope. This is installed in the library, and nothing further is required from the user. But if you want to manually control the scopes, please set the required scopes to the manifest file (appsscript.json) in your client Google Apps Script project.

• https://www.googleapis.com/auth/drive
  • This is used for all methods.
• https://www.googleapis.com/auth/script.external_request
  • This is used for all methods.
• https://www.googleapis.com/auth/documents
  • This is used for Google Document.
• https://www.googleapis.com/auth/spreadsheets
  • This is used for Google Spreadsheet.
• https://www.googleapis.com/auth/presentations
  • This is used for Google Slides.

About including GAS libraries

This library uses the following Google Apps Script library.

• ImgApp

<a name="methods"></a>

Methods

In the current stage, there are the following methods in this library.

<a name="googledocument"></a>

For Google Document

1. getTableColumnWidth()

Retrieve the column width of the table in the Google Document. For example, when a new table, which has 1 row and 2 columns, is manually inserted to the Document body as the default format, the table width and column width retrieved by getColumnWidth() return null. By this, in the current stage, the table width and column width cannot be retrieved. This method achieves this.

Sample script

const documentId = "###"; // Google Document ID
const res = DocsServiceApp.openByDocumentId(documentId).getTableColumnWidth();
console.log(res);

Result

[
  {
    "tableIndex": 0, // 0 means the 1st table in Google Document.
    "unit": "pt",
    "tableWidth": 451.3, // Table width
    "tebleColumnWidth": [225.65, 225.65] // Column width of each column. Array index is the column index.
  },
  ,
  ,
  ,
]

• For example, when the table which has the columns "A" and "B" of 100 pt and 200 pt are checked by above script, the same values of 100 and 200 for the columns "A" and "B" could be confirmed. So from this result, it is found that the column width of DOCX data and Google Document is the same.

<a name="googlespreadsheet"></a>

For Google Spreadsheet

1. getImages()

Retrieve images in and over the cell from Google Spreadsheet as blob. In the current stage, there are no methods for retrieving the images over the cells and inner the cells in the existing Google Spreadsheet service and Sheets API. This method achieves this.

Sample script

const spreadsheetId = "###"; // Google Spreadsheet ID
const res = DocsServiceApp.openBySpreadsheetId(spreadsheetId)
  .getSheetByName("Sheet1")
  .getImages();
console.log(res);

In this script, the images are retrieved from "Sheet1" of spreadsheetId.

And

const spreadsheetId = "###"; // Google Spreadsheet ID
const res = DocsServiceApp.openBySpreadsheetId(spreadsheetId).getImages();
console.log(res);

In this script, the images are retrieved from all sheets of spreadsheetId.

When you want to save all images in the Spreadsheet as the files, you can use the following script.

const spreadsheetId = "###"; // Google Spreadsheet ID
const res = DocsServiceApp.openBySpreadsheetId(spreadsheetId).getImages();
const folder = DriveApp.getFolderById("### folderId ###");
res.forEach(({ images }) =>
  images.forEach((e) => {
    if (e.image) folder.createFile(e.image.blob);
  })
);

Result

[
  {
    "range": { "col": 3, "row": 8, "a1Notation": "C8" },
    "image": {
      "description": "sample description",
      "title": "sample title",
      "blob": BLOB,
      "innerCell": false // "false" means that the image is over a cell.
    }
  },
  {
    "range": { "col": 2, "row": 2, "a1Notation": "B2" },
    "image": {
      "description": "sample description",
      "title": "sample title",
      "blob": BLOB,
      "innerCell": true // "true" means that the image is in a cell.
    }
  },
  ,
  ,
  ,
]

• You can create the image file from BLOB.

• When getSheetByName() is not used, above array is put in each sheet as follows.

  [
    { "sheetName": "Sheet1", "images": [[Object], [Object], [Object]] },
    { "sheetName": "Sheet2", "images": [] },
    { "sheetName": "Sheet3", "images": [[Object], [Object]] }
  ]
  

Limitation

• When the images are retrieved from XLSX data, it seems that the image is a bit different from the original one. The image format is the same. But the data size is smaller than that of the original. When the image size is more than 2048 pixels and 72 dpi, the image is modified to 2048 pixels and 72 dpi. Even when the image size is less than 2048 pixels and 72 dpi, the file size becomes smaller than that of original one. So I think that the image might be compressed. Please be careful this.
• In the current stage, the drawings cannot be retrieved yet. I apologize for this.

2. getComments()

Retrieve comments in Google Spreadsheet. In the current stage, there are no methods for retrieving the comments with the cell coordinate in the existing Google Spreadsheet service and Sheets API. This method achieves this.

Sample script

const spreadsheetId = "###"; // Google Spreadsheet ID
const res = DocsServiceApp.openBySpreadsheetId(spreadsheetId)
  .getSheetByName("Sheet1")
  .getComments();
console.log(res);

In this script, the images are retrieved from "Sheet1" of spreadsheetId.

And

const spreadsheetId = "###"; // Google Spreadsheet ID
const res = DocsServiceApp.openBySpreadsheetId(spreadsheetId).getComments();
console.log(res);

In this script, the images are retrieved from all sheets of spreadsheetId.

Result

[
  {
    "range": {
      "col": 2,
      "row": 11,
      "a1Notation": "B11"
    },
    "comment": [
      {
        "user": "user name",
        "comment": "comment"
      },