Get image file links from google drive to Google Sheet cell

 

By using formulas, scripts and buttons we can prepare a complete staff records form. In this form we can assign buttons such as entering the data to the data sheet, search details and clear the form by using script and buttons. 

Also, get image file url links in google sheet cell to display or enter the data along with other other details. 

We have detailed script and formulas  below to use this form effectively to maintain the staff records. 

SCRIPT

function GdriveFiles() {

   const folderId = 'YOUR GOOGLE DRIVE FOLDER LINK HERE'

  const folder = DriveApp.getFolderById(folderId)

  const files = folder.getFiles()

  const source = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = source.getSheetByName('images');

  const data = [];   

  while (files.hasNext()) {

      const childFile = files.next();

      var info = [ 

        childFile.getName(), 

        childFile.getUrl(),

        childFile.getLastUpdated(),

        Drive.Files.get(childFile.getId()).lastModifyingUser.displayName     

      ];

        data.push(info);

  }

  sheet.getRange(2,1,data.length,data[0].length).setValues(data);

}

 

//==========================================================================//

 

function submitData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var formSub = ss.getSheetByName ("Form"); 

  var range     = ss.getRangeList(['C3', 'G3','C6','G6','C9','G9','C11','G11','J11','C17','E17','C20','E20']);  

  var datasheet = ss.getSheetByName("Data"); 

  

  //Input Values

  var values = [[formSub.getRange("C3").getValue(),

                 formSub.getRange("G3").getValue(),               

                 formSub.getRange("C6").getValue(),

                 formSub.getRange("G6").getValue(),                

                 formSub.getRange("C9").getValue(),

                 formSub.getRange("G9").getValue(),                 

                 formSub.getRange("C11").getValue(),

                 formSub.getRange("G11").getValue(),                 

                 formSub.getRange("J11").getValue(),

                 formSub.getRange("C17").getValue(),

                 formSub.getRange("E17").getValue()]];            

                 datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 11).setValues(values);

                 range.clear();

}

 

//======================================================================//

 

var SEARCH_NAME = 0;

function Search() {

  

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var MainForm = ss.getSheetByName("Form"); 

  var str = MainForm.getRange("M11").getValue();

  var values = ss.getSheetByName("Data").getDataRange().getValues();

  for (var i = 0; i < values.length; i++) {

    var row = values[i];

    if (row[SEARCH_NAME] == str) {

 

                MainForm.getRange("C3").setValue(row[0]);

                MainForm.getRange("G3").setValue(row[1]);                

                MainForm.getRange("C6").setValue(row[2]);

                MainForm.getRange("G6").setValue(row[3]);

                MainForm.getRange("C9").setValue(row[4]);

                MainForm.getRange("G9").setValue(row[5]);                

                MainForm.getRange("C11").setValue(row[6]);

                MainForm.getRange("G11").setValue(row[7]);

                MainForm.getRange("J11").setValue(row[8])                

                MainForm.getRange("C17").setValue(row[9]);                 ;

                MainForm.getRange("E17").setValue(row[10]); 

                MainForm.getRange("C20").setFormula('=E17-C17');

                MainForm.getRange("E20").setFormula('=30-C20');                       

    } }}

//==========================================================================//

 

function clearForm() {

  var sheet = SpreadsheetApp.getActive().getSheetByName('Form');

  var rangesToClear = ['C3','G3','C6','G6','C9','G9','C11','G11','J11','M11','C17','E17','C20','E20' ];

  for (var i=0; i<rangesToClear.length; i++) { 

    sheet.getRange(rangesToClear[i]).clearContent();   

  }

}

 

//====================================================//

FORMULAS

for image display cell  

 =image(SUBSTITUTE(TRIM(J11),"open?id",üc?export=download&id"))

for convert url format   

=IFERROR(ARRAYFORMULA(CONCAT(CONCAT("https://drive.google.com/thumbnail?id=",(REGEXEXTRACT($B$2:$B,"([-\w]{25,})"))),"&sz=w960-h540")),)

Watch Video ------>