CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Steve Rittner
Steve Rittner

Reputation: 11

How do I get a filename from a column of URL's in google sheets

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

Upvotes: 1

Views: 5593

Answers (1)

Cooper
Cooper

Reputation: 64032

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;
}

Upvotes: 2

Related Questions