CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
datafarmer
datafarmer

Reputation: 43

Open Google sheet with script

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.

Upvotes: 0

Views: 1680

Answers (2)

Cooper
Cooper

Reputation: 64052

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.

Upvotes: 1

Wicket
Wicket

Reputation: 38160

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

Upvotes: 1

Related Questions