How to get in Apps Script the value of a dropdown in a Google Doc?

Question

Google Docs now supports dropdown inputs (Insert > Dropdown). I am using Apps Script to access a Google Docs, and would like to get the value of a drop down.

I have read Get Google "pills" value as Plain text from Google docs with Google Apps Script, but unfortunately this solution doesn't work.

However, when getting the element in my Apps Script, it looks like the element is unsupported.

const statusCell = table.getRow(1).getCell(1);
const p = statusCell.getChild(0).asParagraph();
const c = p.getChild(0);
console.log("---> " + c);
console.log("tpe: " + c.getType());
// ---> UnsupportedElement
// tpe: UNSUPPORTED

If I replace the content of the cell with plain text, then everything works. I'm only having issues with drop downs.

Is it possible to get the value of a drop down in Google Docs from Apps Script?

Efta

What's it's type c.getType()? – Roborant

The type is UNSUPPORTED. – Efta

Answer 1

I believe your goal is as follows.

• You want to retrieve the values of table cells.
• The values of table cells are the dropdown list of the smart chips.

Issue and workaround:

Unfortunately, in the current stage, it seems that there are no built-in methods of Google Apps Script for directly retrieving the values of the dropdown list of the smart chips. When getType() is used, UNSUPPORTED is returned as you have already mentioned in the comment. And also, even when Google Docs API is used, "content": "\n", is returned. In this case, I would like to recommend reporting this to the Google issue tracker as a future request.

From the above situation, I would like to propose a workaround. In this workaround, the Google Document is converted to DOCX data. And, the DOCX data is converted to Google Document. By this conversion, the texts of the smart chips can be retrieved. When this flow is reflected in a script, it becomes as follows.

Sample script:

Please copy and paste the following script to the script editor of Google Document. And, please enable Drive API at Advanced Google services.

function myFunction() {
  const doc = DocumentApp.getActiveDocument();
  const id = doc.getId();
  const url = "https://docs.google.com/feeds/download/documents/export/Export?exportFormat=docx&id=" + id;
  const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
  const tempFileId = Drive.Files.insert({ title: "temp", mimeType: MimeType.GOOGLE_DOCS }, blob).id;
  const tempDoc = DocumentApp.openById(tempFileId);
  const table = tempDoc.getBody().getTables()[0];
  for (let r = 0; r < table.getNumRows(); r++) {
    const row = table.getRow(r);
    for (let c = 0; c < row.getNumCells(); c++) {
      const cell = row.getCell(c);
      console.log({ row: r, col: c, text: cell.getText() });
    }
  }

  // DriveApp.getFileById(tempFileId).setTrashed(true); // If you want to delete the tempolary document, please use this.
  // DriveApp.createFile(); // This is used for automatically detecting the scope by the script editor.
}

• When this script is run, the texts of the table, which has the dropdown list of the smart chips, can be retrieved.

Testing:

When this script is tested to the above Document, the following result is obtained.

{ row: 0, col: 0, text: 'sample1' }
{ row: 0, col: 1, text: 'sample2' }
{ row: 0, col: 2, text: 'sample3' }
{ row: 1, col: 0, text: 'sample3' }
{ row: 1, col: 1, text: 'sample2' }
{ row: 1, col: 2, text: 'sample1' }

Note:

• Of course, I think that by parsing DOCX data, you can also retrieve the values from the table. But, I thought that when Document service (DocumentApp) can be used, when it is used, the script will be simpler. So, I proposed to convert from DOCX to Document.

Religieuse

Thank you, this is working great. Converting the file to a different format is an interesting workaround. Note to other people who may look into this solution: The conversion is apparently pretty slow (~10s for a single document), and the above script doesn't include code to delete the converted file. – Efta

@Efta Thank you for replying. About the above script doesn't include code to delete the converted file., I have added a script of DriveApp.getFileById(tempFileId).setTrashed(true) as a comment line. About The conversion is apparently pretty slow (~10s for a single document), I deeply apologize for this. – Religieuse

Came across the same issue and this answer saved the day! Thanks @Religieuse – Cotquean

@Cotquean Thank you for your comment. I'm glad about it. – Religieuse

Answer 2

This function shows how to find all the dropdowns in a doc and get their type name and current values without creating a temporary file in Google Drive:

function getDropDownValuesFromDoc(doc) {
  const id = doc.getId();
  const url = "https://docs.google.com/feeds/download/documents/export/Export?exportFormat=docx&id=" + id;
  const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
  blob.setContentType("application/zip");
  const content = Utilities.unzip(blob);
  let xml = "";
  for (let file of content) {
    if (file.getName() == "word/document.xml") {
      xml = file.getDataAsString();
    }
  }
  var dropDownValues = XmlService.parse(xml)
    .getRootElement()
    .getDescendants()
    .filter(e => e.getType() == "ELEMENT")
    .filter(e => e.asElement().getName() == 'dropDownList')
    .map(e => ({
      type: e.getParentElement().getChildren()[0].getAttributes()[0].getValue(),
      currentValue: e.asElement().getAttributes()[0].getValue()
    }));
  return dropDownValues;
}

In order for this function to work, you will need to manually add OAuth scopes to your script. Follow these instructions: https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes and add the following scopes:

  "oauthScopes": [
    "https://www.googleapis.com/auth/documents",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/drive"
  ],

Leninism

Answer 3

For those interested, there is a quicker possibility for when iterating through many files.

Converting to DOCX and then back to DOC works great for individual files, but when iterating through (hundreds) of files, as was my case, it was too slow and would time out.

Instead, I converted to HTML and then used XmlService.parse() to XML, which I could then navigate using traditional getChild() methods. Because, in my case, the information I need to extract from the dropdown is always in the first table in the Google Doc, and the structure of that table doesn't change, it worked perfectly.

This reduced the time for checking 391 documents from 51 minutes to 8.15!

Here is an example of my script:

// Get the document as HTML
let url = `https://docs.google.com/feeds/download/documents/export/Export?exportFormat=html&id=${id}`;
let docHtml = UrlFetchApp.fetch(url, {
  headers: { authorization: 'Bearer ' + ScriptApp.getOAuthToken() }
}).getContentText()

// Extract the first table.
let tableHtml = docHtml.match(/<table[^>]*>[\s\S]*?<\/table>/i)[0]

var entities = {
  '&nbsp;': '\u00A0',
  '&middot;': '\u00B7',
  '&rsquo;': '\u2019',
  '&ldquo;': '\u201C',
  '&rdquo;': '\u201D',
  '&mdash;': '\u2014',
  '&ndash;': '\u2013'
};

// Replace all occurrences of non-standard HTML entities with their Unicode equivalents
tableHtml = tableHtml.replace(/&\w+;/g, function (match) {
  return entities[match] || match;
});

// Parse XML
var document = XmlService.parse(tableHtml);
// Get the first table element in the document
var table = document.getRootElement();
// Get the third cell in the first row (index 0, index 2)
var satisfactionCell = table.getChildren('tr')[0].getChildren('td')[2];
var satisfaction = satisfactionCell.getChild('p').getChild('span').getText().trim();
// Get the second cell in the third row (index 2, index 1)
var notesCell = table.getChildren('tr')[2].getChildren('td')[1];
var notes = notesCell.getChild('p').getChild('span').getText().trim();
// Return the text content of the cells
return [satisfaction, notes];

This extracts a "Satisfaction" dropdown (e.g. "Very satisfied") and notes left by a client.

Considerable