Use Microsoft Docs Files (Word, Excel, and PowerPoint) with Document Service, Spreadsheet Service, and Slides Service of Google Apps Script

GasTips / library
Google Apps Script

Gists

Description

Recently, Microsoft Docs files (Word, Excel, and PowerPoint files) could be manually edited by opening it on Google Drive using the browser. This is good news for a lot of users. With this situation, the URLs of Microsoft Docs files were changed. For example, when a Microsoft Word file (DOCX) is opened on Google Drive with the browser, the URL is https://docs.google.com/document/d/###/edit. ### of this URL is the file ID of the DOCX file on Google Drive. This URL is the same as that of Google Document while the length of the file ID is different. From this situation, it might be guessed that the Document service of Google Apps Script might be able to be used for DOCX files.

Recently, I sometimes saw this situation at Stackoverflow. However, unfortunately, in the current stage, Microsoft Docs files cannot be directly used with the Document service, Spreadsheet service, and Slides Service of Google Apps Script. Fortunately, when Microsoft Docs files are converted to Google Docs files, those services can be used. I’m worried that this process might be a bit complicated for users.

In this report, I would like to introduce the sample scripts for using Microsoft Docs files with Document service, Spreadsheet service, and Slides Service of Google Apps Script.

IMPORTANT

When Microsoft Docs files (Word, Excel, and PowerPoint files) are updated, these sample scripts overwrite the Microsoft Docs files. So, I would like to recommend testing the sample scripts after you back up your original files.

Limitations

  • In the current stage, Microsoft Docs files are not completely same with Google Docs files. When Microsoft Docs files are updated using Document service, Spreadsheet service, and Slides service, there might be a situation that the updated result is completely same between Microsoft Docs files and Google Docs files.

Usage

1. Open script editor

Create a Google Apps Script project and open the script editor of Google Apps Script.

2. Install library

In this report, a Google Apps Script library is used. Ref

In order to use this library, please install the library as follows.

  • Install this library.

    • Library’s project key is 1B0eoHz03BVtSZhJAocaGNq94RjoXocz8xGMaLzwVdmAvYW5k8s5Yd360.

3. Sample scripts

1. Use Word file (DOCX)

Sample script is as follows.

If you want to only get values from DOCX file, you can use the following sample script.

function sample() {
  const fileId = "###"; // File ID of DOCX file.

  const MD = MicrosoftDocsApp.setFileId(fileId);
  const doc = MD.getDocument();

  const res = doc.getBody().getText();
  console.log(res);
  MD.end();

  // DocumentApp.getActiveDocument(); // This comment line is used for automatically detecting a scope "https://www.googleapis.com/auth/documents" by the script editor. So, please don't remove this comment line.
}

If you want to put values to DOCX file, you can use the following sample script. In this case, please use saveAndClose(). By this, DOCX file is updated.

function sample() {
  const fileId = "###"; // File ID of DOCX file.

  const MD = MicrosoftDocsApp.setFileId(fileId);
  const doc = MD.getDocument();

  const body = doc.getBody();
  body.appendParagraph("sample");
  MD.saveAndClose();
  MD.end();

  // DocumentApp.getActiveDocument(); // This comment line is used for automatically detecting a scope "https://www.googleapis.com/auth/documents" by the script editor. So, please don't remove this comment line.
}

In this sample script, doc of the Class Document object can be used by the auto-completion of JSDoc in the library.

2. Use Excel file (XLSX)

Sample script is as follows.

If you want to only get values from XLSX file, you can use the following sample script.

function sample() {
  const fileId = "###"; // File ID of XLSX file.

  const MD = MicrosoftDocsApp.setFileId(fileId);
  const ss = MD.getSpreadsheet();

  const res = ss.getSheets()[0].getDataRange().getValues();
  console.log(res);
  MD.end();

  // SpreadsheetApp.getActiveSpreadsheet(); // This comment line is used for automatically detecting a scope "https://www.googleapis.com/auth/spreadsheets" by the script editor. So, please don't remove this comment line.
}

If you want to put values to XLSX file, you can use the following sample script. In this case, please use saveAndClose(). By this, XLSX file is updated.

function sample() {
  const fileId = "###"; // File ID of XLSX file.

  const MD = MicrosoftDocsApp.setFileId(fileId);
  const ss = MD.getSpreadsheet();

  ss.getSheets()[0].appendRow([new Date(), "sample"]);
  MD.saveAndClose();
  MD.end();

  // SpreadsheetApp.getActiveSpreadsheet(); // This comment line is used for automatically detecting a scope "https://www.googleapis.com/auth/spreadsheets" by the script editor. So, please don't remove this comment line.
}

In this sample script, ss of the Class Spreadsheet object can be used by the auto-completion of JSDoc in the library.

3. Use PowerPoint file (PPTX)

Sample script is as follows.

If you want to only get values from PPTX file, you can use the following sample script. In this case, a text is retrieved from a shape on the first slide.

function sample() {
  const fileId = "###"; // File ID of PPTX file.

  const MD = MicrosoftDocsApp.setFileId(fileId);
  const s = MD.getSlide();

  const res = s.getSlides()[0].getShapes()[0].getText().asString();
  console.log(res);
  MD.end();

  // SlidesApp.getActivePresentation(); // This comment line is used for automatically detecting a scope "https://www.googleapis.com/auth/presentations" by the script editor. So, please don't remove this comment line.
}

If you want to put values to PPTX file, you can use the following sample script. In this case, please use saveAndClose(). By this, PPTX file is updated.

function sample() {
  const fileId = "###"; // File ID of PPTX file.

  const MD = MicrosoftDocsApp.setFileId(fileId);
  const s = MD.getSlide();

  s.getSlides()[0]
    .insertShape(SlidesApp.ShapeType.RECTANGLE, 0, 0, 100, 100)
    .getText()
    .setText("new text");
  MD.saveAndClose();
  MD.end();

  // SlidesApp.getActivePresentation(); // This comment line is used for automatically detecting a scope "https://www.googleapis.com/auth/presentations" by the script editor. So, please don't remove this comment line.
}

In this script, a rectangle shape including a text is created to the first slide.

Also, in this sample script, s of the Class Presentation object can be used by the auto-completion of JSDoc in the library.

Reference

 Share!