How can I transform a link to a file name?

Question

I have a Google spreadsheet file with almost 2000 rows of URL such as this one:

https://docs.google.com/uc?id=0B4ptELk-D3USblk1aWd1OWowRWs

Each URL contains an image, stored in Google Drive. My question is: How can I transform this link ( https://docs.google.com/uc?id=0B4ptELk-D3USblk1aWd1OWowRWs) to a file name? For instance, I would like to know if it could be a /something/folder/Photofile1.jpg.

Is there anyway of doing this?

Try using scripts: [Drive App](https://developers.google.com/apps-script/reference/drive/drive-app), [related Q on SO](http://stackoverflow.com/questions/25684098/how-to-make-a-list-on-google-spreadsheet-of-all-files-in-a-google-drive-folder) — Max Makhrov, Mar 01 '17 at 10:12
This excel corresponds to a form. Each row, has the URL (picture) of an unique item. Listing all the files is not enough, because I will not be able to map each row to it's correspondant URL (thus loosing the useful information of each row). — Pedro Beirao, Mar 01 '17 at 10:20
Please, give the access to any file, so I could test the script. My files have link like: `https://drive.google.com/file/d/ key ...` so I'm not sure the code would work with link: `https://docs.google.com/uc?id=...` — Max Makhrov, Mar 01 '17 at 10:32
Link for the shared image: https://drive.google.com/uc?id=0B4ptELk-D3USVi1NZ3ZGbkhqYXc - You can also check the sample document at https://docs.google.com/spreadsheets/d/1hCJA7zo6zd73WIHKGgie1QH1ZEmMmi5FwirNLEAkcrs/edit?usp=sharing — Pedro Beirao, Mar 01 '17 at 10:56

Max Makhrov · Accepted Answer · 2017-03-02T07:04:57.637

0

Try script:

function TESTgetFileName() {
  Logger.log(getFileName('0B4ptELk-D3USVi1NZ3ZGbkhqYXc'));
  //                      ^^^^^^^^^^ file ID ^^^^^^^^^
}


function getFileName(id) {
  var file = DriveApp.getFileById(id);
  var fileName = file.getName();    
  var strFolders = getFolders(file); 
  return strFolders + '/' + fileName;  
}

function getFolders(object) {

  var folders = object.getParents();
  if (!folders.hasNext()) { return 'My Drive'; } 
  var folder = folders.next();

  var folderNames = [];

  while (folder.getParents().hasNext()) {        
    var folderName = folder.getName();
    folderNames.unshift(folderName);
    folder = folder.getParents().next();
  }

  return folderNames.join('/');

}

Script function will return the result:

0B4ptELk-D3USVi1NZ3ZGbkhqYXc → My Drive/something/folder/Photofile1.jpg

Tests

I opened script editor, selected function TESTgetFileName and get the result

My Drive/Detalhes_Farmacia.Fotografia_Fachada_1.JPEG.

This function can be run from script, not directly from spreadsheets. When you try using it as custom formula from sheet, it throws error:

You do not have permission to call getFileById (line 8).

So better use it from script to get data and then write the result to the sheet.

edited Mar 02 '17 at 07:04

answered Mar 01 '17 at 12:12

Max Makhrov

17,309
5
55
81

I ran the script. I get the following error when I try the TESTgetFileName and the getFileName: "You do not have permission to call getFileById (line 8)." For the getFolders I get the following: ´TypeError: Cannot find function getParents in object 0B4ptELk-D3USVi1NZ3ZGbkhqYXc. (line 16).´ – Pedro Beirao Mar 01 '17 at 21:00
I've tested the function. See **Tests** in the answer – Max Makhrov Mar 02 '17 at 07:05
Indeed, it works from the script editor. Is there anyway to have it working on the sheet? I have almost 2000 rows with links, it will be hard to run one by one. If there is a way to run on the sheet, I can display the result in a colum B next to the URL in column A. – Pedro Beirao Mar 02 '17 at 08:24
No, but you can handle it with script: get all 2000 rows data into script array, then just run through array elements, get file names and then write the result into sheet. – Max Makhrov Mar 02 '17 at 08:30
It works. Thank you for your support. However, in terms of efficiency, it will take "forever". Each row can has an average of 4 pictures, multiplied by 2000 rows, it will be a nightmare to pass it all to the script editor and then to copy each column result and copy in the sheet. There must be an easier way to use a function on the sheet that can "call" your script and return the result at a given cell on the sheet. Is there any limitation for this? – Pedro Beirao Mar 02 '17 at 08:47
Of course, you don't need to run every value manually. Use standart functions: `SpreadsheetApp.getSheetByName(...)`, `sheet.getRange(...)`, `range.getValues()`. Learn more here https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app. The goal of SO is to give answers on particular questions, not to make all code work for you. – Max Makhrov Mar 02 '17 at 08:51
If you'll have any problem, please post another specific question. Please mark the answer as correct, if it matches the question. – Max Makhrov Mar 02 '17 at 09:18

How can I transform a link to a file name?

1 Answers1