title: Using Apps Script w/ Google Sheets: Get short URL & fetch file name
body: <p><a href="https://docs.google.com/spreadsheets/d/1sQyBTh7xCptnvr2SOQyEovmgIi6U-UapvDQOphk2Qxg/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1sQyBTh7xCptnvr2SOQyEovmgIi6U-UapvDQOphk2Qxg/edit?usp=sharing</a></p>
  
  <p>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. </p>
  
  <p>I have two requirements for an App Scripts job:</p>
  
  <pre><code>    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.
  </code></pre>
  
  <p>Error:"No item with the given ID could be found, or you do not have permission to access it."</p>
  
  <p>I will paste my App Scripts code in a moment. </p>
  
  <p>This is what I have done thus far:</p>
  
  <pre><code>    1) Enabled both the Drive API &amp; 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.
  </code></pre>
  
  <p>Here is the code:</p>
  
  <pre><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 &lt; 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 &lt; 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);
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>Here's my Question:</p>
  
  <p><strong><em>What can I do to remove the hidden restrictions for file access?</em></strong></p>
  
  <p>I found the first 2/3 of this code (onOpen &amp; short functions) along with a great explanation <a href="http://webapps.stackexchange.com/questions/76050/google-sheets-function-to-get-a-shortened-url-from-bit-ly-or-goo-gl-etc">here</a>. Thanks, Alex! </p>