Search code examples
google-apigoogle-docsgoogle-docs-api

A script to update linked sheet from google doc

I have linked part of a sheet in google doc (as linked object). Now, whenever I change the sheet data, I can click a button in google doc and the data is reflected in the google doc linked sheet too (this is all built in google doc stuff).

What I want to do is the other side of this. I am able to see a bunch of data in one place (google doc) based on the sheets I have linked. I would like to update the data in the google doc, and "upload" it to the linked google sheets.

I am trying to write a script to do that. But cannot seem to find any method to access linked sheets. I found this slides API page that can does the sheet -> slide syncing.

I am looking at the document API page, but I scanning through add... and get... methods, I don't see to find any way to get linked objects. Is it represented as NamedRange? If so, how do I access it?

There was another similar question, but without any satisfactory answer.

If you can share some pointers to get started, I would appreciate it.

Edit: Here is an example doc (and a spreadsheet contained their in) to explain the situation clearer. Test document for updating spreadsheet - Google Docs

Solution

  • You can find the Table elements in your Document via findElement(elementType). If that's the only Table in your Document, as in the sample you shared, this is immediate.

    Once you retrieve the Table, you can loop through its rows and cells and come up with a 2D array with the values from the table:

    function getTableValues() {
  const doc = DocumentApp.getActiveDocument();
  const body = doc.getBody();
  const table = body.findElement(DocumentApp.ElementType.TABLE).getElement();
  let tableValues = [[]];
  for (let i = 0; i < table.getNumRows(); i++) {
    const tableRow = table.getRow(i);
    for (let j = 0; j < tableRow.getNumCells(); j++) {
      const tableCell = tableRow.getCell(j);
      const text = tableCell.getText();
      tableValues[i].push(text);
    }
    if (i == table.getNumRows() - 1) break; 
    tableValues.push([]);
  }
  return tableValues;
}

    Once you've done that, you just need to copy it to your spreadsheet via setValues(values):

    function copyToSheet(tableValues, spreadsheetId) {
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Sheet1");
  sheet.getRange(1, 1, tableValues.length, tableValues[0].length).setValues(tableValues);  
}

    Calling both of these in the same function, you would get this:

    function main() {
  const tableValues = getTableValues();
  const spreadsheetId = "{your-spreadsheet-id}";
  copyToSheet(tableValues, spreadsheetId);
}

    Note: