Import media files in bulk

Prepare image URLs in a Google sheet for bulk import

This procedure assumes you keep your media files in Google Drive, and that you have authorization to share the file URLs as public links.

1. Open a Google Sheet.
2. Go to Extensions > App Scripts.
3. Paste in the code below.
4. Give the file a name, such as Get Folder Details.
5. Then Save.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Get Folder Details')
    .addItem('Retrieve Files', 'retrieveFiles')
    .addToUi();
}

function retrieveFiles() {
  var folderLink = SpreadsheetApp.getUi().prompt('Enter the folder link').getResponseText();
  var folder = DriveApp.getFolderById(getFolderIdFromLink(folderLink));
  var files = folder.getFiles();
  var data = [
    ['Name', 'Import Url', 'Type', 'ProductCode']
  ];

  while (files.hasNext()) {
    var file = files.next();
    var fileUrl = 'https://drive.google.com/uc?export=download&id=' + file.getId();
    var filename = file.getName();
    var filenameWithoutExt = filename.slice(0,filename.lastIndexOf('.'));
    data.push([filenameWithoutExt, fileUrl, 'Image', '']);
  }

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getDataRange().clearContent();
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

  SpreadsheetApp.getUi().alert('Files retrieved successfully.');
}

function getFolderIdFromLink(link) {
  var regex = /\/folders\/([^/?]+)/;
  var match = link.match(regex);

  if (match && match[1]) {
    return match[1];
  }

  return null;
}

The new function is added as a menu in the spreadsheet.

Add media to the spreadsheet

1. Select Get Folder Details > Retrieve Files.
2. Follow the prompts to navigate to your media drive.
3. Select the relevant folder and add files.
4. This bulk adds media as URLs in the spreadsheet.
5. Repeat for each folder of media you want to add.