Programmatically insert pictures into Google Drive
/*
1. Custom Menu
2. Prompt
3. Sheet code
*/
/**********************************************
Creates a custom menu to execute other functions
**********************************************/
function onOpen() {
var ui = SpreadsheetApp.getUi(); // Gets the spreadsheet's Ui
// Or DocumentApp or FormApp.
ui.createMenu('Picture Menu') // Creates the menu
.addItem('Get Pictures', 'menuItem1') // Adds the first item
//.addItem('Menu Item 2 Label', 'menuItem2')
//.addItem('Menu Item 3 Label', 'menuItem3')
//.addItem('Menu Item 4 Label', 'menuItem4')
//.addSeparator() // How to add separators
//.addItem('Menu Item 5 Label', 'menuItem5')
//.addSubMenu(ui.createMenu('Sub-menu Label') // This is not a clickable item... it is just a label
//.addItem('Menu Item 6 Label', 'menuItem6'))
.addToUi(); // Adds it to the Ui
}
// These are commented out to add menuItems more easily in the future...
// just change the functions inside..
function menuItem1() {
list_all_files_inside_one_folder_without_subfolders();
}
/////////////////////////////////////////////////////////////////////////////////////
/******************************
This is a prompt template that returns the response to be used in another function:
(var returnedValue = functionName();)
It can tell whether you clicked cancel or exited and do something with those results
******************************/
function promptFolderId() { // Function name
var ui = SpreadsheetApp.getUi();
var response = ui.prompt('In which folder are the pictures saved?','Click on the folder that houses the pictures, copy the folder ID, and paste it in the box below\n\n (Everything after...https://drive.google.com/drive/folders/THIS_IS_THE_FOLDER_ID...COPY_ME)', ui.ButtonSet.OK_CANCEL);
if (response.getSelectedButton() == ui.Button.OK) { // If the user clicked ok, get their response
//Logger.log(response.getResponseText());
return response.getResponseText();
}
}
////////////////////////////////////////////////////////////////////////////////////
/**********
* Gets active spreadsheet
* Gets sheet by name
* Gets folder by id
* Creates a new array for the list
* Pushes the Name, URL, ID, and Size of each file into the array
* Clears the previous list
* Lists out the documents' properties on the named sheet
* Sorts the sheet ascending by the first column
***********/
/*
* Inserts note with some instructions
* Concatenates sharing URL with the image function
*/
function list_all_files_inside_one_folder_without_subfolders(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pictures");
var folder = DriveApp.getFolderById(promptFolderId()); // change the folder ID here
var lastColumn = sheet.getLastColumn();
var lastRow = sheet.getLastRow();
var list = [];
var newNote = sheet.getRange(1,1)
.setNote("1. Copy the Folder ID of where the pictures are saved in your Google Drive\n2. Set sharing to Anyone with the Link\n3. Run the code in the Picture Menu\n4. After you run it one time, you should be able to set the sharing permissions back to Only You Can View\n5. Enjoy!");
list.push(['Image Name','Stable Image URL','Image Preview']);
var files = folder.getFiles();
while (files.hasNext()){
file = files.next();
var row = [];
row.push(file.getName(),"http://drive.google.com/uc?export=view&id="+file.getId(), "=image(B2)");
list.push(row);
}
//Clears the past entries
sheet.clear();
//Sets the values of the array
sheet.getRange(1,1,list.length,list[0].length).setValues(list);
var imageFormula = sheet.getRange(2,3).getFormula();
var setImageFormula = sheet.getRange(2,3,lastRow,1).setFormula(imageFormula);
}