I recently switched from our agency using Trello as our project management system to actually using Google Sheets instead and the transition as been amazing except one thing… We weren’t able to easily upload files directly to a task as we could with Trello. So I knew you could do a lot through app scripts in Google Sheets so I looked for a couple of solutions and yes the theory was proven in 2014 but the code was out of date and not working, so I had my developer updated the code and now it works flawlessly. Here are instructions if you want to have the capabilities of uploading a file directly to a google sheet and only display the Google Drive URL in the cell.
Here is a Google Sheet SAMPLE, just make a copy and edit.
Instructions
- Go to > Tools > Script editor
- Provide a name for the Script (we used Upload_Files)
- Copy and paste the below code in the “Code.gs” file
- NOTE: Be sure to insert your Google Drive folder id
Folder_Id = 'YOUR FOLDER ID HERE'
function onOpen(e){
var ss = SpreadsheetApp.getActiveSpreadsheet()
var menuEntries = [];
menuEntries.push({name: "File", functionName: "doGet"});
ss.addMenu("Attach", menuEntries);
}
function upload(obj) {
var file = DriveApp.getFolderById(Folder_Id).createFile(obj.upload);
var activeSheet = SpreadsheetApp.getActiveSheet();
var File_name = file.getName()
var value = 'hyperlink("' + file.getUrl() + '";"' + File_name + '")'
var activeSheet = SpreadsheetApp.getActiveSheet();
var selection = activeSheet.getSelection();
var cell = selection.getCurrentCell()
cell.setFormula(value)
return {
fileId: file.getId(),
mimeType: file.getMimeType(),
fileName: file.getName(),
};
}
function doGet(e) {
var activeSheet = SpreadsheetApp.getActiveSheet();
var selection = activeSheet.getSelection();
var cell = selection.getCurrentCell();
var html = HtmlService.createHtmlOutputFromFile('upload');
SpreadsheetApp.getUi().showModalDialog(html, 'Upload File');
}
-
- Still in the Script Editor click on File > New > HTML script
- Insert the following code in that file between the two body tags
<!DOCTYPE html> <html> <head> <base target="_top"> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css"> </head> <body> <form> <!-- Modified --> <div id="progress" ></div> <input type="file" name="upload" id="file"> <input type="button" value="Submit" class="action" onclick="form_data(this.parentNode)" > <input type="button" value="Close" onclick="google.script.host.close()" /> </form> <script> function form_data(obj){ // Modified alert(obj.nodeName) google.script.run.withSuccessHandler(closeIt).upload(obj); }; function closeIt(e){ // Modified console.log(e); google.script.host.close(); }; </script> </body> </html>
- Still in the Script Editor click on File > New > HTML script
- Go back to your google sheet and refresh that page, then you should now see an “attach” button in the menuSelect a cell and hit attach and upload a file and watch the cell populate with a google drive attachment URL
