Google-sheets – Linked spreadsheet data to document

google docsgoogle sheets

I have created a Google document that links to tables in a Google spreadsheet.

Now I want to copy both the spreadsheet and the document.

However, the link remains to the original spreadsheet.

Where can I set the filename of the copy of the spreadsheet?

Best Answer

in the spreadsheet, you need to look for import formulas like =IMPORTRANGE() where you need to change URL or ID

in the document, you need to look for link menu appearing on the top right of your imported table and by selecting change range you can change the source

Code

// global
var ss = SpreadsheetApp.getActive();

function onOpen() {
  var menu = [{name: "Create Document", functionName: "createDoc"}];
  ss.addMenu("Extra", menu);
}

function createDoc() {
  var sh = ss.getActiveSheet(), aRow = sh.getActiveCell().getRowIndex();
  var title = sh.getRange(aRow, 1).getValue(), docDate = new Date(); 
  var docTitle = title + "-" + docDate, doc = DocumentApp.create(docTitle);
  var url = doc.getUrl();

  // create app and panel
  var app = UiApp.createApplication().setTitle("Open Google Document")
    .setHeight(50).setWidth(400);
  var vPanel = app.createVerticalPanel()
    .add(app.createAnchor(docTitle, url));

  // add to app
  app.add(vPanel);
  ss.show(app);  
}

Screenshot

enter image description here

Remarks

This piece of code creates a new menu entry called "Extra" upon opening of the file. The selection "Create Document" will create (in the back gound) a Google Document and displays a panel with a hyperlink to open the document.

Example

I've created an example file for you: How To Open Document

Best Answer

Related Solutions

Google Docs – Add Relative Link to Header Using URL Fragment

Google Docs – Automatically Open Created Text Document

Code

Screenshot

Remarks

Example

Related Topic