1

I can't even begin to log the hours I have spent trying to figure this out, albeit it has been very illuminating teaching myself how to code throughout the journey.

Can anyone please piggyback off of Diego's code (seen here: Get filename from url google sheet) to get the names of non spreadsheet files? I have done everything within my limited knowledge and I am very stuck.

I essentially have a column of URL's on a spreadsheet that I would like a script to get the filenames from. As the ID stays static but the filename can change, I want to be able to have a second column of 'updated' file names that correspond with the static ID.

I've attached a link to the sheet for reference. Thanks SO much to anyone who can shed some light on this!

https://docs.google.com/spreadsheets/d/1pBSGc92tGj0l-JlhQznsGfGT1Nfj2LqNN7pyM4M_69c/edit#gid=2015441637

Steve Rittner
  • 11
  • 1
  • 3
  • Can you add your current script and explain about the issue of your script to your question? I think that by this, it will help users think of about your issue and solutions. – Tanaike Mar 02 '19 at 23:27
  • Take a look at this [https://stackoverflow.com/a/16840612/7215091](https://stackoverflow.com/a/16840612/7215091) – Cooper Mar 03 '19 at 01:23
  • Hey Tanaike, thank you for your response! I was out of town for work and am just coming back down to earth. I will put something together for context this afternoon. – Steve Rittner Mar 12 '19 at 15:39

1 Answers1

2

Getting File Names from URls

function getIdFromUrl(url) { 
  return url.match(/[-\w]{25,}/); 
}

function getFileNames() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Data');
  var rg1=sh.getRange(2,4,getColumnHeight(4,sh,ss)-1,1);//current column of urls
  var vA1=rg1.getValues();
  var rg2=sh.getRange(2,5,getColumnHeight(4,sh,ss)-1,1);//new column of file names
  var vA2=rg2.getValues();
  for(var i=0;i<vA1.length;i++){
    vA2[i][0]=DriveApp.getFileById(getIdFromUrl(vA1[i][0])).getName();
  }
  rg2.setValues(vA2);
}

function getColumnHeight(col,sh,ss){
  var ss=ss || SpreadsheetApp.getActive();
  var sh=sh || ss.getActiveSheet();
  var col=col || sh.getActiveCell().getColumn();
  var lastrow=sh.getLastRow();
  if(lastrow==0)return 0;
  var rg=sh.getRange(1,col,lastrow,1);
  var vA=rg.getValues();
  while(vA.length>0 && vA[vA.length-1][0].length==0){
    vA.splice(vA.length-1,1);
  }
  return vA.length;
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Cooper, this worked! Like perfectly. Tanaike suggested I post the current script for some more context, so I'll be back with that shortly. I think it's something you'll dig. – Steve Rittner Mar 12 '19 at 15:41
  • Cooper , @Tanaike , please view the updated spreadsheet link below. I've included a readme explaining each step. Please let me know if you have any follow-up for me, and thanks for looking under the hood! https://docs.google.com/spreadsheets/d/123YhERv5CuYASxuyIls8l_H0TGU5qY9WERKwzjWSL0U – Steve Rittner Mar 12 '19 at 17:04