Change linked table source in Google Doc

Question

Appreciate any help I can get on this one.

I am using a script to automatically push content from a spreadsheet to a document as a sort of super mail merge. Changing the text in the document is no problem, but getting the tables in the document to behave has proved more difficult.

Every table in the document is linked to the spreadsheet. What I'm trying to do, with limited luck, is any of the following:

Change the source table on the document from one spreadsheet to another, then update all the values.
Update all the source tables on the document, then unlink from the source.
Directly copy ranges from a spreadsheet to a document.

I haven't had any luck so far. The code below is where I'm up to. the getLinkUrl() attribute returns null for every table found in the document. Any pointers would be appreciated.

function updateTables(documentID,spreadsheetID){

  var ui = SpreadsheetApp.getUi();

//Check if input was passed
  if(documentID === undefined){
    var documentURL = ui.prompt('Table Update Module','Please enter the url of the document you wish to update tables on.', ui.ButtonSet.OK_CANCEL);
//Check for cancel
    if(documentURL.getSelectedButton() == ui.Button.OK){ 
      documentID = getIdFromUrl(documentURL.getResponseText());
    }else{
      Logger.log('Exit when asked for document URL.');
      return;}
  }
  Logger.log('Replacement document ID: ' + documentID);

//Check if input was passed
  if(spreadsheetID === undefined){
    var spreadsheetURL = ui.prompt('Table Update Module','Please enter the url of the spreadsheet you wish to get the tables from.', ui.ButtonSet.OK_CANCEL);
//Check for cancel
    if(spreadsheetURL.getSelectedButton() == ui.Button.OK){ 
      spreadsheetID = getIdFromUrl(spreadsheetURL.getResponseText());
    }else{
      Logger.log('Exit when asked for spreadsheet URL.');
      return;}
  }
  Logger.log('Replacement spreadsheet ID: ' + spreadsheetID);

//Get tables
  var documentTables = DocumentApp.openById(documentID).getBody().getTables();

//Loop through tables
  for(var i = 0; i < documentTables.length; ++i){
    Logger.log('Table URL: ' + documentTables[i].getLinkUrl());
  }  
}

@jess, thank you for the suggestion, but checking for null values is not the problem. Using getLinkUrl() for tables does not return the linked URL of the table, it returns any hyperlinks which have been assigned to that table. — healey

Александр Ермолин Александр Ермолин · Accepted Answer · 2019-02-01T15:17:21

Setting a link URL to the table does not mean any automatic data updates. This is only a hyperlink to some other resource, nothing more. If you had not set the link explicitly before, using Table.setLinkUrl() method, you will get null from every call of Table.getLinkUrl().

Below is a sample code for the document having at least 1 table, where the method returns a string value, not null.

function getLinkUrl() {
  var doc = DocumentApp.getActiveDocument();
  var tables = doc.getBody().getTables();
  tables[0].setLinkUrl('https://stackoverflow.com');
  Logger.log(tables[0].getLinkUrl());
}

Change linked table source in Google Doc

1 Answers