Open Google sheet with script

Question

I would like to open each file in a folder via script. Below is what I have tried. This is my first shot at any script in sheets.

This code runs but does not open file, I could be a mile off on this but I can't figure out how to make it open the files. Thanks for your help

function myFunction() {
  var dApp = DriveApp;
  var folder = dApp.getFoldersByName("test").next();
  var filesIter = folder.getFiles(); 

  while(filesIter.hasNext()) {
    var files = filesIter.next();
    var ss = SpreadsheetApp.open(files);  
  }  
}

I simply want it to open all the files in a folder in this case "test". there are currently two google sheets in the folder.

When you say "open", are you imagining that each file opens in a new browser tab? Because that is very different from `SpreadsheetApp.open()` and would likely not be possible purely in Apps Scripts. Or are you having issue using the `ss` variable after assigning the result of `.open()`? — Joshua T, May 28 '19 at 22:15

score 1 · Answer 1 · answered May 28 '19 at 22:45

Google Apps Script server-side code can't do actions on the client side like opening a spreadsheet on the user web browser but you could use HTML Service to use client side code to achieve your goal.

score 1 · Answer 2 · answered May 28 '19 at 23:26

You can read open files from Scripts but not in the way that users can open files. You can open them on the server and read and/or write data but the file doesn't open up in edit mode like it does when you open it as a user.

Here's a script that opens up spreadsheets and reads their name and id and returns it to a webapp.

HTML:

<html>
<head><title></title>
</head>
<body>

<div id="data"></div>


<script>
  window.onload=function(){
    google.script.run
    .withSuccessHandler(function(ssA){
      var html='';
      for(var i=0;i<ssA.length;i++) {
        html+='<br />' + 'Name: ' + ssA[i].name + ' Id: ' + ssA[i].id;
      }
      document.getElementById('data').innerHTML=html;

    })
    .readSpreadsheetsInAFolder();
  }
  </script>
  </body>
  </html>

CODE.gs:

function readSpreadsheetsInAFolder() {
  var ssA=[];
  var folders=DriveApp.getFoldersByName("LinkedSpreadsheets");
  while(folders.hasNext()){
    var folder=folders.next();
    var files=folder.getFilesByType(MimeType.GOOGLE_SHEETS);
    while(files.hasNext()) {
      var file=files.next();
      var ss=SpreadsheetApp.openById(file.getId());
      ssA.push({name:file.getName(),id:file.getId()});
    }
  }
  return ssA;
}

This function was written for my account so you may have to modify the Folder Name to get it to work on your account.

Open Google sheet with script

2 Answers2