Google-sheets – Google Docs (or Sheets) API to scroll the view

google docsgoogle sheets

If I want to scroll to a particular paragraph in Google Docs, or a particular cell in Google spreadsheets, is there a way to do that via Google Apps Script?

Best Answer

For Google Doc Spreadsheet, you just need to set the active selection, the scroll is then done directly:

var file = SpreadsheetApp.getActiveSpreadsheet();
var sheet = file.getActiveSheet();
var row = LineToScrollTo;
file.setActiveCell(sheet.getRange(row,1));

Related Solutions

Google Sheets – Selecting a Sequence of Sheets in Google Docs/Spreadsheets

Update based on OP's feedback

What might help you then is =INDIRECT

For example: =INDIRECT("Sheet" & ROW() & "!A1")

What this formula does is uses the current sheet's own row to help define what information is being pulled. In this case, starting with cell A1, the formula will be evaluated as =INDIRECT("Sheet1!A1")

What =INDIRECT does is allows you to use text strings to define reference ranges when pulling data, thus you can use & to concatenate a string and insert an automated way of iterating through the various sheet numbers. To get the summarized data in A1 of all sheets, just drag the formula in the summary sheet all the way down the column to have the formula auto-fill each of the following roles. Here's a screenshot:

enter image description here

What's happening in my example is that on Sheet1 I have "Status" in cell A1. Looking at my formula in columnC, you'll notice that =INDIRECT is pulling that value from A1. The following entries is from autofilling (click and drag) the formula down to other rows, making =INDIRECT concatenate different Sheet #s based on the row().

OLD ANSWER Which does not use =INDIRECT

Without knowing more about the format of your specific Groups' sheets, you have it almost correct in your example.

If you rename sheets in your document to a custom name, you need to reference them using quotes. If you add single quotes around the sheet name, you've got it:

Group name  

='Group1'!A1  
='Group2'!A1  
='Group3'!A1  
...

You can also add summary information in a sheet, like:

Groups Total Sum

=sum('Group1'!B1:B13)

That will give you the sum of values in Sheet Group1, column B, rows 1 - 13.

Note: if you're ever confused as to how to reference a value from another sheet, you can always just:

Go to the summary sheet
Select the cell you'd like to edit
Hit = to start a formula and then
Click to another sheet/cell and see how the formula bar autofills the information.

Google-sheets – Place cell data from Sheets into Docs

The following piece of code will create a Google Document and will insert a dynamic text, as a paragraph, into the newly created document. All is done from within a Google Spreadsheet.

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);

  // create text in document
  var body = doc.getBody();
  body.appendParagraph("A paragraph " + docTitle); 

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

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

Add

The code in your Google Spreadsheet, under Tools>Script editor. Press the bug button to authenticate the code and all is set to go.

Example

I've created an example file for you: create Google Document with text