I had a programmer help me write a plugin that will export a .CSV file next to your project with the following info: file names, timecode in, timecode out, and duration (even if they're in precomps). You can import the CSV file into Google Sheets and export a PDF if you need it in that format. You can download it here.
Also, once you get it into Google Sheets you can use the script below to clean up your file list, it'll do things like getting rid of duplicate rows, combine overlapping files, etc...
Here's how to add the script to your Sheet: go to Extensions > App Scripts, click on the + (Add a file), choose Script, then copy and paste the script below into the code window and save. To install the script go to Extensions > Macros > Import macro > runAllScripts (at the bottom). To run the script go to Extensions > Macros > runAllScripts to run the script. You'll probably get a warning that you have to agree too then after that you have to run the script again for it to actually run. It'll run from the bottom up so scroll down and you can watch it work.
function runAllScripts() {
deleteRowAndColumn();
removeFileExtensions();
removeDuplicateRows();
cleanUpTimecodes();
}
function deleteRowAndColumn() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
sheet.deleteRow(1);
sheet.deleteColumn(5);
}
function removeFileExtensions() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var extensionsToRemove = ['.psd', '.jpg', '.mov', '.mp4', '.png', '.jpeg', '.aif'];
var newValues = values.map(function(row) {
return row.map(function(cell) {
var newCell = cell.toString();
extensionsToRemove.forEach(function(ext) {
newCell = newCell.replace(ext, '');
});
return newCell;
});
});
range.setValues(newValues);
}
function removeDuplicateRows() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getDataRange();
var values = range.getValues();
var numRows = range.getNumRows();
var uniqueRows = [];
var duplicateRowsIndexes = [];
for (var i = 0; i < numRows; i++) {
var row = values[i];
var isDuplicate = uniqueRows.some(function(uniqueRow) {
return row.every(function(cell, index) {
return cell === uniqueRow[index];
});
});
if (!isDuplicate) {
uniqueRows.push(row);
} else {
duplicateRowsIndexes.push(i + 1);
}
}
duplicateRowsIndexes.reverse().forEach(function(index) {
sheet.deleteRow(index);
});
}
function cleanUpTimecodes() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getValues();
var cleanedData = [];
var timecodeMap = {};
data.forEach(function (row) {
var fileName = row[0];
var timeIn = row[1];
var timeOut = row[2];
var duration = row[3];
if (timecodeMap.hasOwnProperty(fileName)) {
var existing = timecodeMap[fileName];
var existingTimeIn = existing[1];
var existingTimeOut = existing[2];
if (isTimecodeLessThanOrEqual(timeIn, existingTimeOut)) {
if (isTimecodeGreaterThanOrEqual(timeOut, existingTimeOut)) {
existing[2] = timeOut;
existing[3] = calculateDuration(existing[1], timeOut);
}
} else {
cleanedData.push(existing);
timecodeMap[fileName] = row;
}
} else {
timecodeMap[fileName] = row;
}
});
for (var key in timecodeMap) {
cleanedData.push(timecodeMap[key]);
}
sheet.clearContents();
sheet.getRange(1, 1, cleanedData.length, cleanedData[0].length).setValues(cleanedData);
}
// Helper functions for cleanUpTimecodes
function isTimecodeLessThanOrEqual(time1, time2) {
return convertTimecodeToFrames(time1) <= convertTimecodeToFrames(time2);
}
function isTimecodeGreaterThanOrEqual(time1, time2) {
return convertTimecodeToFrames(time1) >= convertTimecodeToFrames(time2);
}
function convertTimecodeToFrames(timecode) {
var parts = timecode.split(':');
return parseInt(parts[0]) * 86400 + parseInt(parts[1]) * 3600 + parseInt(parts[2]) * 60 + parseInt(parts[3]);
}
function calculateDuration(timeIn, timeOut) {
var framesIn = convertTimecodeToFrames(timeIn);
var framesOut = convertTimecodeToFrames(timeOut);
var durationInFrames = framesOut - framesIn;
return convertFramesToTimecode(durationInFrames);
}
function convertFramesToTimecode(frames) {
var hours = Math.floor(frames / 86400);
var minutes = Math.floor((frames % 86400) / 3600);
var seconds = Math.floor((frames % 3600) / 60);
var framesLeft = frames % 60;
return pad(hours) + ':' + pad(minutes) + ':' + pad(seconds) + ':' + pad(framesLeft);
}
function pad(number) {
return ('00' + number).slice(-2);
}
I made the scripts for Google Sheets using Chat GPT so if you need to make any changes to it and aren't a programmer feed the current script into Chat GPT and have it write you an updated one. Things you might want to change in this script: the framerate (it's for 24fps projects), the removal of file extensions, the combining of overlapping files/timecodes into a single row.
1 Correct answer
15
Replies
AdChoices