Last active
February 21, 2024 18:58
-
-
Save dab2020/7ac83cbd79dbf22c989e9aa061dfceac to your computer and use it in GitHub Desktop.
Google Drive File type and generate downlaod link
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function getFileDetailsAndGenerateLink() { | |
// Get the active Google Spreadsheet | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); | |
// Get the data range of the sheet | |
var dataRange = sheet.getDataRange(); | |
var data = dataRange.getValues(); | |
// Iterate through each row starting from the second row (assuming headers in the first row) | |
for (var i = 1; i < data.length; i++) { | |
// Get the File ID from the third column (column index 2) | |
var fileId = data[i][2]; | |
// Check if the fileId is not empty | |
if (fileId) { | |
// Get the file extension from the fileId | |
var fileExtension = getFileExtension(fileId); | |
// Generate the direct download link for the fileId | |
var downloadLink = "https://drive.google.com/uc?export=download&id=" + fileId; | |
// Update the fourth and fifth columns (columns index 3 and 4) | |
sheet.getRange(i + 1, 4).setValue(fileExtension); | |
sheet.getRange(i + 1, 5).setValue(downloadLink); | |
} | |
} | |
} | |
// Function to get the file extension from the fileId | |
function getFileExtension(fileId) { | |
var file = DriveApp.getFileById(fileId); | |
var fileName = file.getName(); | |
var fileExtension = fileName.split('.').pop(); | |
return fileExtension; | |
} |
Author
dab2020
commented
Jan 21, 2024
function getFileDetailsAndGenerateLink() {
// Get the active Google Spreadsheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get the data range of the sheet
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
// Iterate through each row starting from the second row (assuming headers in the first row)
for (var i = 1; i < data.length; i++) {
// Get the File ID from the third column (column index 2)
var fileId = data[i][2];
// Check if the fileId is not empty
if (fileId) {
// Get the file extension from the fileId
var fileExtension = getFileExtension(fileId);
// Generate the direct download link for the fileId
var downloadLink = "https://drive.google.com/uc?export=download&id=" + fileId;
var name = getFilenam(fileId)
// Update the fourth and fifth columns (columns index 3 and 4)
sheet.getRange(i + 1, 4).setValue(fileExtension);
sheet.getRange(i + 1, 5).setValue(downloadLink);
sheet.getRange(i + 1, 6).setValue(name);
console.log("Executing");
console.log("Data Row #" + i + "Picture Name: " + name );
console.log("***************")
}
}
}
// Function to get the file extension from the fileId
function getFileExtension(fileId) {
var file = DriveApp.getFileById(fileId);
var fileName = file.getName();
var fileExtension = fileName.split('.').pop();
return fileExtension;
}
function getFilenam(fileId) {
var file = DriveApp.getFileById(fileId);
var fileNam = file.getName();
return fileNam;
}
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment