How to Bulk Download Player Image/Video Submissions from Gametize using Google Sheets & Apps Script!

Credits to Kingsley Tan

Our platform doesn’t currently support completion reports with images for direct download, but here’s a workaround using Google Sheets & App Script! 

1️⃣ Download Submission Reports

• Go to the challenge where players submitted their images/videos.
• Click View All Completions → Select Download Report
• This will contain hyperlinks to the media files. 📁

2️⃣ Set Up Google Drive Folder

• Create a folder in Google Drive where you want to store the images/videos.

3️⃣ Open Google Sheets & Apps Script

• Open a new Google Sheet.
• Click on File →  Import →  Upload the downloaded Report → Click on Replace spreadsheet
• Click Extensions → Apps Script
• Delete any existing code & paste the script below. 

function autoDownloadMediaToDrive() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var folderId = "YOUR_FOLDER_ID"; // Replace with your Google Drive folder ID
  var folder = DriveApp.getFolderById(folderId);
  
  var range = sheet.getDataRange(); // Get all data in the sheet
  var values = range.getValues();

  for (var row = 1; row < values.length; row++) { // Start from row 2 (skip header)
    for (var col = 0; col < values[row].length; col++) {
      var cellValue = values[row][col];

      if (isValidMediaUrl(cellValue)) {
        try {
          var response = UrlFetchApp.fetch(cellValue, {muteHttpExceptions: true});
          if (response.getResponseCode() === 200) {
            var blob = response.getBlob();
            var extension = getFileExtension(cellValue);
            var fileName = "media_" + row + "_" + col + "." + extension;
            var file = folder.createFile(blob.setName(fileName));
            sheet.getRange(row + 1, col + 2).setValue(file.getUrl()); // Store link in next column
          }
        } catch (e) {
          sheet.getRange(row + 1, col + 2).setValue("Error: " + e.message);
        }
      }
    }
  }

  SpreadsheetApp.getUi().alert("Media download completed!");
}

function isValidMediaUrl(url) {
  var regex = /(https?:\/\/.*\.(?:png|jpg|jpeg|gif|bmp|svg|mp4|mov|avi|mkv|webm))/i;
  return regex.test(url);
}

function getFileExtension(url) {
  var match = url.match(/\.(png|jpg|jpeg|gif|bmp|svg|mp4|mov|avi|mkv|webm)$/i);
  return match ? match[1] : "unknown";
}

4️⃣ Replace "YOUR_FOLDER_ID"

• Go to the folder in Google Drive
• Look at the URL
• Copy the string that appears after "/folders/"
• For example, if the URL is "https://drive.google.com/drive/folders/1dyUEebJaFnWa3Z4n0BFMVAXQ7mfUH11g", then the folder ID is "1dyUEebJaFnWa3Z4n0BFMVAXQ7mfUH11g". 
• Click Save project to Drive

5️⃣ Run the Script

• Click Run → Select autoDownloadMediaToDrive
• Grant permissions when prompted.

That’s it! Your images/videos will now be stored in Google Drive with links saved in your Sheet