Drive folder - URL Link - Google Sheets

[Chris Kearney]

Looking for Appscript code to automate video communication. There are three actions to perform. 

1. Convert all video files in a google drive folder to URL Links

2. Place those links within a designated google sheet. 

3. Automate each time a new video file comes in the drive folder without the need to refresh manually. 

Workflow: Screencastify Video - Driver Folder - Google Sheet - Appsheet. I need help with the middle part. 

Much appreciated. 

[Laurie Nason]

Hi Chris,

Probably what I would do is the following in a master google sheet:

• Have one tab where you put all your settings into it - such as the URL/ID of the folder(s) so that your script can refer to it
• Have another tab where you keep a log of the files it finds and the process of moving it - good to keep an eye on what the script has done while you are absent from it
• Have a script that checks the specific video folder for any new items (AFAIK you can't have a trigger on a drive folder, but you can have a timed trigger on a sheet)
• Inside the script if it finds a new video, move the file to a "Processed" folder, and write out the file's URL and other details into the google sheet for your next stage in the process

Here is some code that I use to do something like this (mine is recursive though going into folders underneath the main folder.

Laurie

//--------------------------------------------------------------------------------

// Set of functions to read (recursively) a top level folder, get the files information

// and then add the info to a sheet for the files that are there.

// USE AT YOUR OWN RISK!

// Very little error checking!!

// If a file is found - the function gets its information (line 69 currently) and writes it to the sheet

// It also changes the view permissions information to the file so that it can be accessed anywhere with the link

// There's also a description added to the file property to do a partial check and update any new files found

// Uses named ranges in the sheet to hold the URL information, and last imported date info

// Only logs to built in Logger

//--------------------------------------------------------------------------------

// The main function to extract the images from the folders

function extractImagesFull() {

start(true);

}

function extractImagesUpdate() {

start(false);

}

/*

Recursively add a list of files from a named folder to a sheet

*/

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FullPhotoInformation');

function start(fullScan=false) {

if(fullScan){

//clear sheet if we are starting from scratch

sheet.getRange('A3:K').clear();

}

var folderId=getIdFromUrl(SpreadsheetApp.getActiveSpreadsheet().getRangeByName('RootFolderUrl').getValue());

Logger.log(folderId);

var folder=DriveApp.getFolderById(folderId);

Logger.log('Processing root folder: '+ folder.getName());

processFolder(folder,'',fullScan);

Logger.log('file scrape complete');

return;

}

function processFolder(folder,parentName,fullScan) {

Logger.log('New folder: '+folder.getName());

var contents = folder.getFiles();

if(contents.hasNext()){

addFilesToSheet(contents, folder,parentName,fullScan);

}

var subFolder = folder.getFolders();

while (subFolder.hasNext()){

processFolder(subFolder.next(),folder.getName());

}

return;

}

function addFilesToSheet(files, folder,parentName,fullScan) {

var data=[];

var folderName = folder.getName();

while (files.hasNext()) {

var file = files.next();

var tmp_filename=file.getName();

var tmp_desc=file.getDescription();

//check to see if we have found this photo before by checking description - if it's blank we haven't, so need to add it - If function times out - need to be careful here as it may not be written into sheet at timeout time

if(!file.getDescription()||fullScan){

//Check File Sharing Access - needs to be Anyone on the internet

if(file.getSharingAccess()!=DriveApp.Access.ANYONE_WITH_LINK){

//need to change it here

file.setSharing(DriveApp.Access.ANYONE_WITH_LINK,DriveApp.Permission.VIEW);

}

//set description

file.setDescription('Found: '+new Date())

// and add into sheet by adding to data array

data.push([

//sheet.appendRow([

parentName +'/'+folderName,

file.getName(),

file.getMimeType(),

file.getDateCreated(),

file.getUrl(),

file.getLastUpdated(),

file.getDescription(),

file.getSize(),

file.getId(),

file.getSharingPermission(),

file.getSharingAccess()

]);

}

}

if(data.length>0){

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

}

//set last updated date to now

SpreadsheetApp.getActiveSpreadsheet().getRangeByName('LastImported').setValue(new Date());

SpreadsheetApp.flush();

}

function getIdFromUrl(url) {

return url.match(/[-\w]{25,}/);

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d6383e1c-da89-4510-a174-d06d98cdc567n%40googlegroups.com.

[Matthew Moran]

I created a Google Sheet, with a sheet named, "File List". I created a header row with:

FILE ID, File Name, Fild Modified, File MIME Type, File URL

This sheet is in the same folder as the video files (which is probably not the ideal set up but works for this. If you create a folder with appropriate permissions, you can replace the appropriate lines of code below and return the folder by ID, etc.

I return more information than is required but find that, in most apps I want access to this information. Just modify the code as fits your situation.

function buildVideoList() {

  const activeWS = SpreadsheetApp.getActiveSpreadsheet();

  const fileListSht = activeWS.getSheetByName("File List");

  const wsId = activeWS.getId();

  const driveFolder = DriveApp.getFileById(wsId).getParents().next();

  const driveFolderId = driveFolder.getId();

  let allFiles = driveFolder.getFiles();

  let fileList = [];

  while (allFiles.hasNext()) {

    let curFile = allFiles.next();

    let fileName = curFile.getName();

    let fileType = curFile.getMimeType();

    let fileId = curFile.getId();

    let fileUrl = curFile.getUrl();

    let fileMod = curFile.getLastUpdated();

    if (fileType.includes("video")) {

      let addArray = [fileId,fileName,fileMod,fileType,fileUrl]

      fileList.push(addArray);

    }

  }

  // because I rebuild the list each time, I delete the current data, leaving the header's in tact. 

  let lastRow = fileListSht.getLastRow();

  fileListSht.getRange(2,1,lastRow - 1,5).clear();

  // place your new list of media files in the sheet

  fileListSht.getRange(2,1,fileList.length,5).setValues(fileList);

}

[Matthew Moran]

Oh.. I forgot to add.

There is no trigger for when I new file is added.

You could accomplish a timed trigger that runs every minute (probably a bit much) or every X minutes. It could check for any files modified since the last time the script was run or close to it.

How are the files being placed in the folder?

On Friday, May 6, 2022 at 6:04:53 AM UTC-7 Chris Kearney wrote:

[Geoffrey Callaghan]

https://fabform.io/a/how-to-find-google-sheet-id

[Chris Kearney]

Hypothetically, they are placed directly from a third party screen recorder system. Thank you for the script!