Open Google sheet with script

0
votes

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.

2
google-apps-scriptgoogle-sheets
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

2 Answers

1
votes

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.

Related

1
votes

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.