CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jonathan Bryant
Jonathan Bryant

Reputation: 23

How do I manage Google App Script Projects so that they point to a file chosen by the user?

I have a script that I want to execute against data in a Sheets file. The file changes weekly, so I was hoping I could prompt the user to select the file before the script runs. When I google that, the examples I see build the UI from the scratch and involve multiple html files. Is it really that complex or am I missing something obvious? Is there a built in function to allow for the file picker dialogue to be used? My current solution is listed below, but requires that I train the user how to find the Sheet ID.

function showInputBox() {
  
  var ui = SpreadsheetApp.getUi();
  var input = ui.prompt("Enter the document ID #: ");

}

Upvotes: 0

Views: 45

Answers (1)

Daniel Manship
Daniel Manship

Reputation: 61

I've done something similar things using DriveApp functions like getFilesByName , which can work if the filename structure each week is predictable. For example something like

thisweeksheet = DriveApp.*folder identifier*.getFilesByName("Weekly sheet " + week).next()

where the "week" variable is the identifier for that week and perhaps inputted by the user. the getFilesByName function returns a list of all files in the folder with that name, .next() returns the first file in the list. If there's only one file with that name in the folder is will be the correct one.

one you have the file you need to get the id so you can open within SpreadsheetApp

thisweekid = thisweeksheet.getId()
SpreadsheetApp.getById(thisweekid)

This is driveapp documentation

Upvotes: 1

Related Questions