How do I create a Sheets Function to get my 'File Names' & 'Download Link'

[Frans Baud]

Hi everybody 👋

I need some help getting the script below to become fully accessible in Google Sheets 

•  I want it to be able to execute via a Google Sheet formula e.g. =myFunction("160JIT6FeYj1POyn9dwLMeH9RVbU9es6i")
• Add the content in my current active sheet through the method SpreadsheetApp.GetActiveSpreadsheet().GetActiveSheet();

The script below already works:

• Currently it creates a new Spreadsheet called 'ListFiles_Output' in the Root of My Drive

function listFolderContents() {

const ListOfFiles = 'ListFiles_Output';

const id = '160JIT6FeYj1POyn9dwLMeH9RVbU9es6i';

const folder = DriveApp.getFolderById(id);

const files = folder.getFiles();

let ss = SpreadsheetApp.get

let sheet = ss.getActiveSheet();

sheet.appendRow( ['name', 'link'] );

while(files.hasNext()) {

let file = files.next();

let fileName = file.getName();

Logger.log(file.getName());

let fileLink = file.getDownloadUrl();

Logger.log(file.getDownloadUrl());

sheet.appendRow( [fileName, fileLink] );

}

};

[Waqar Ahmad]

You can not call DriveApp from a function which is run as Custom Function/Formula

Take a look here at the Google Help Documentation to know which services you can call from a custom function/formula

https://developers.google.com/apps-script/guides/sheets/functions#using_services