CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
Mikah
Mikah

Reputation: 75

How to access to the google sheet the short cut ist pointing at?

[![enter image description here][1]][1]I have made a shortcut (new feature of google) of a google sheets.

Now I want to get all the Spreadsheets I have in a directory: these function does not work anymore to treat all the Spreadsheet: folder.getFilesByType(mimeType) because the 'new' shortcut a new mime type has been introduced.
The ID of the shortcut is different from the original file. You can not access anymore over ID of the files you find in the directory.

  1. What is the solution to select only spreadsheets and spreadsheets shortcuts in a directory?
  2. How to open the spreadsheet a shortcut is pointing at?
    function myFunction() {
      var ss=SpreadsheetApp.getActiveSheet();
      var idRepertoire=ss.getRange("A1").getValue();

      var monRep=DriveApp.getFolderById(idRepertoire);

      var fichiers=monRep.getFiles();


      while (fichiers.hasNext()) {
        var fichier=fichiers.next();
        var parentFichiers=fichier.getParents();
        while (parentFichiers.hasNext()){
          var parent=parentFichiers.next();
          var identiteF=fichier.getId();
          ss.appendRow([fichier.getName(),fichier.getMimeType(),identiteF,parent.getName()]);
          SpreadsheetApp.openById(identiteF).getSheetName("test").getRange("A1"); // causes an error for the shortcut
        }
      }

    }


  [1]: https://i.sstatic.net/grcin.png

Upvotes: 1

Views: 777

Answers (1)

ale13
ale13

Reputation: 6052

Both of your questions can be solved by using the Drive API.

Explanation

In order to retrieve all the spreadsheets and the spreadsheet shortcuts, you can use the GET request to retrieve the files:

  • For spreadsheet types:
GET https://www.googleapis.com/drive/v3/files

With the following parameter for the q field

mimeType = 'application/vnd.google-apps.spreadsheet'
  • For shortcut types:
GET https://www.googleapis.com/drive/v3/files

With the following parameter for the q field

mimeType = 'application/vnd.google-apps.shortcut'

Afterwards, if you want to retrieve the files from the shortcuts above, you can simply do a GET request for each shortcut. So for one shortcut, you can do something like this, where fileId is in fact the id of the shortcut.

GET  https://www.googleapis.com/drive/v3/files/fileId

With the following parameters for the fields field

shortcutDetails

After this GET request, you will receive a response in which you can find the mimeType of the shortcut as well as the original id of the file from the shortcut. 

{
 "shortcutDetails": {
  "targetId": "ID_OF_THE_ORIGINAL_FILE",
  "targetMimeType": "MIME_TYPE_OF_THE_ORIGINAL_FILE"
 }
}

Reference

Upvotes: 1

Related Questions