Using Apps Script w/ Google Sheets: Get short URL & fetch file name

Question

https://docs.google.com/spreadsheets/d/1sQyBTh7xCptnvr2SOQyEovmgIi6U-UapvDQOphk2Qxg/edit?usp=sharing

Above is my Google Sheets file which is linked to a form that uploads receipt image files and catalogs the info for the uploaded receipt.

I have two requirements for an App Scripts job:

    1) Get a short URL for the uploaded file. 
         STATUS: ACHIEVED!!!
    2) Fetch the name of the uploaded file for reference. 
         STATUS: NOT ACHIEVED, GET ERROR CODE BELOW WHEN CLICKING MENU ITEM
         TO RUN THE SCRIPT.

Error:"No item with the given ID could be found, or you do not have permission to access it."

I will paste my App Scripts code in a moment.

This is what I have done thus far:

    1) Enabled both the Drive API & URL Shortener APIs in the script
    2) Enabled both APIs on Google API Console for this project
    3) Saved my code in Apps Script
    4) Ran onOpen() function to establish the Custom Menu in my spreadsheet 
       (it adds the menu when I refresh the spreadsheet).
    5) I am the owner of all of the following: 
            +the Drive account ALL of the pertinent files are stored on, 
            +the App Script that was created, 
            +the project in the Google API console.
    6) I have given consent to access the process when the Script first runs 
       with the same Google Account.

Here is the code:

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu("URL Manager")
  .addItem("Shorten","short")
  .addItem("Get File Names","names")
  .addToUi();
}

function short() {
  var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
  var output1 = [];
  for(var i = 0, iLen = data.length; i < iLen; i++) {
    var url = UrlShortener.Url.insert({longUrl: data[i][0]});
    output1.push([url.id]);
  }
  //puts the short url one column to the right
  range.offset(0,1).setValues(output1);
}

function names() {
  var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
  //var data must be converted to string since it is the long url of the 
  //file, then parsed for index of "=" (var n), then I sliced out the fileID 
  //with var m
  var str = data.toString();
  var n = str.indexOf("=");
  var m = str.slice(n+1)
  var output2 = [];
  for(var i = 0, iLen = data.length; i < iLen; i++) {
    var file = DriveApp.getFileById({fileID: m[i][0]});
    output2.push([file.getName()]);
  }
  //puts the file name 1 column to the left
  range.offset(0,-1).setValues(output2);
}

So I reload my Spreadsheet, click to activate the cell(s) I want to get short url and file name for, then click the menu items to execute the script functions. short() works, names() does not. See the error I get, above.

Here's my Question:

What can I do to remove the hidden restrictions for file access?

I found the first 2/3 of this code (onOpen & short functions) along with a great explanation here. Thanks, Alex!

Nancy Sukup · Accepted Answer · 2018-03-08 21:58:18Z

1

Ed Nelson, you were absolutely correct!

The problematic line of code was this:

var file = DriveApp.getFileById({fileID: m[i][0]});

This piece

    {fileID: m[i][0]}

returns [fileID] inside brackets, while the DriveApp.getFileByID() utility requires a string input (cannot read when '[]' are included).

Here's the working solution:

 for (var i=0; i < data.length; i++) {
    var fileID = data[i][0].toString();
    var file = DriveApp.getFileById(fileID);

TaDa!!!

Thanks, Ed!

answered Mar 8, 2018 at 21:58

Nancy Sukup

114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ed Nelson · Accepted Answer · 2018-03-07 21:31:42Z

0

Try this:

function names() {
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var rng=range.getDataRange()
  var data = rng.getValues();
  var output2 = [];
  for(var i = 1, iLen = data.length; i < iLen; i++) {
    var str = data[i][2].toString();
    var n = str.indexOf("=");
    var m = str.slice(n+1)
    var file = DriveApp.getFileById(m);
    output2.push([file.getName()]);
  }
  for(var j=0;j<output2.length;j++){
  range.getRange(j+2,2).setValue(output2[j][0]);
  }}

answered Mar 7, 2018 at 21:31

Ed Nelson

10.4k2 gold badges32 silver badges29 bronze badges

Collectives™ on Stack Overflow

Using Apps Script w/ Google Sheets: Get short URL & fetch file name

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related