Smart Life US
How to Save Google Sheets Check-in Photos to Drive: Organize by Date with Apps Script
Introduction: Why Do Check-in Photos Scatter Every Time?
Once you start running inbound appointments and arrival check-ins in Google Sheets, you eventually hit the same problem. BOL/POD photos from drivers are scattered across email, messengers, and individual Google Drives, and when a claim comes in later, you spend a lot of time trying to find which container’s photo is where. Everyone uses different file naming rules, so search doesn’t work well either.
This post offers a practical solution to reduce that chaos. We’ll walk through how to automatically save BOL/POD photos taken from a Google Sheets check-in screen into date-based folders in Google Drive, and how to leave photo links in the appointment sheet using Google Apps Script. If you’ve already built the check-in screen and appointment lookup following the previous article Google Sheets Container Number Check-in: Build Driver Arrival Appointment Lookup and added GPS validation in Google Sheets Check-in GPS Distance Validation: Block Off-site Check-ins, this part adds Google Sheets automation for logistics check-in photo management on top of that.
The code below is based on a structure that has actually been used in a warehouse operation. To avoid collisions with other parts of this series, it uses the CHK_ prefix. The sheet that stores appointment records is referred to generically as the Appointment Main Sheet, not by any real-world name.
Overall Structure: Saving Google Sheets Check-in Photos to Drive
Most Google Sheets–based check-in screens receive photos in a similar way. A web app or sidebar takes a photo via webcam/mobile camera and turns it into a base64 string (e.g. data:image/jpeg;base64,...), then passes it to a server-side (Google Apps Script) function via google.script.run or an HTTP POST. If you paste the image directly into the sheet, you’ll run into size and speed issues, so a typical flow looks like this:
First, the frontend sends the base64 image string and metadata like container number (or appointment ID) to a server function. In this post, the server entry function is called CHK_saveImageToDrive, and the core logic is separated into an internal function CHK_saveImageToDriveCore_. This separation makes it easy to reuse the same save function from other screens in the future.
Second, the server function validates the incoming string, uses Utilities.base64Decode to create a Blob, then finds (or creates) a date-based folder in Google Drive and stores the image file in it. A simple structure like InboundCheckinPhotos/2026-08-17 proved easiest to manage in practice. Even just having date-based folders lets you see all BOL/PODs for a specific day at a glance.
Third, after creating the file, the function returns its ID and URL, and writes the photo link back into the Appointment Main Sheet. In real-world use you can store a formula like =HYPERLINK("URL","BOL") in the cell, but in many cases it’s enough to store just the URL and let users use filters on the sheet. The crucial point is making it possible to jump directly from “this container” to “its photo” in one step.
Finally, since multiple users may process check-ins at the same time, you should use Google Apps Script’s LockService to avoid collisions when saving concurrently. If you block disallowed MIME types or abnormally large files before sending them to DriveApp, you can avoid filling folders with broken files over time.
Creating Drive Configuration Constants: Fix Folder, Format, and Size Rules
First, define configuration constants for where and how photos will be stored. With these constants in place, you can change folder names or size rules in one place, which makes it easier to handle policy changes later.
- What it does: Defines the Drive root folder name, date folder format, max allowed size (MB), allowed MIME types, and timezone.
- Where to paste: At the very top of
Code.gsunderExtensions → Apps Scriptin Google Sheets, or at the top of the script file related to check-ins. - What to do after pasting: Just save (⌘S or Ctrl+S).
const CHK_DRIVE_CONFIG = { // → Check-in photo storage settings
ROOT_FOLDER_NAME: 'Inbound check-in photo', // → Root folder name
DATE_FOLDER_FORMAT: 'YYYY-MM-DD', // → Date folder format
MAX_IMAGE_MB: 10, // → Max allowed file size (MB)
ALLOWED_MIME_TYPES: ['image/jpeg', 'image/png'], // → Allowed image types
TIMEZONE: 'America/New_York' // → Fixed timezone
}; //
// → This constant uses the CHK_ prefix so its name won’t collide with constants from other parts.How to verify it works: You won’t see any visible changes yet. In the next step you’ll verify it together with the folder creation and save functions that use this constant.
Finding/Creating Date Folders: DriveApp Date-based Folder Creation
Now create a function that automatically creates and reuses date-based folders with DriveApp. When BOL/POD photos are grouped by date, it’s easy to open just the date in question if a claim comes in and scan that day’s photos.
- What it does:
- Finds the root folder (
입고체크인사진), or creates it if it doesn’t exist. - Based on a given date, finds or creates a
YYYY-MM-DDsubfolder.
- Where to paste: Directly below
CHK_DRIVE_CONFIG. - What to do after pasting: Save, then run a little test function to see if today’s folder is created.
function CHK_getOrCreateRootFolder_() { // → Find/create root folder
const folders = DriveApp.getFoldersByName( // → Search folders with same name
CHK_DRIVE_CONFIG.ROOT_FOLDER_NAME // → Use name from config
);
if (folders.hasNext()) { // → If it already exists
return folders.next(); // → Use the first folder
}
return DriveApp.createFolder( // → If not, create new
CHK_DRIVE_CONFIG.ROOT_FOLDER_NAME // → Create with same name
);
}
function CHK_getOrCreateDateFolder_(dateObj) { // → Find/create date folder
if (!(dateObj instanceof Date) || isNaN(dateObj)) { // → Check if date is valid
throw new Error('CHK_getOrCreateDateFolder_: Invalid date.'); // → Block invalid input
}
const tz = CHK_DRIVE_CONFIG.TIMEZONE; // → Use fixed timezone
const y = Utilities.formatDate(dateObj, tz, 'yyyy'); // → Extract year
const m = Utilities.formatDate(dateObj, tz, 'MM'); // → Extract month
const d = Utilities.formatDate(dateObj, tz, 'dd'); // → Extract day
const folderName = [y, m, d].join('-'); // → Combine into YYYY-MM-DD
const root = CHK_getOrCreateRootFolder_(); // → Get root folder
const subFolders = root.getFoldersByName(folderName); // → Look for date folder
if (subFolders.hasNext()) { // → If it exists
return subFolders.next(); // → Use that folder
}
return root.createFolder(folderName); // → If not, create new
}How to verify it works: From a simple test function, call CHK_getOrCreateDateFolder_(new Date()), run it, and then check Drive to see whether a folder like 입고체크인사진/<today’s date> has been created.
Core Photo Save Function: base64 → Blob → Drive Upload
Now for the core of the “save photos from Google Sheets to Drive with Apps Script” logic. This function receives the base64 string of a BOL/POD photo, validates it, uploads it into a date-based folder, and returns an information object.
- What it does:
- Splits a
data:image/jpeg;base64,...string into MIME type and body. - Validates against allowed MIME types and maximum size.
- Creates a Blob and stores it in the date folder.
- Returns file ID, URL, file name, and folder name.
- Where to paste: Under the date-folder functions.
- What to do after pasting: Save, then use a test function to confirm that files are actually being created.
function CHK_saveImageToDriveCore_(base64Data, fileNameHint) { // → Core image save function
if (typeof base64Data !== 'string' || base64Data.indexOf(',') < 0) { // → Check string and format
throw new Error('CHK_saveImageToDriveCore_: Invalid image data.'); // → Block invalid input
}
const parts = base64Data.split(','); // → Split header/body
const header = parts[0]; // → data:...;base64 part
const dataPart = parts[1]; // → Actual base64 body
const mimeMatch = header.match(/data:(.*);base64/); // → Extract MIME
if (!mimeMatch) { // → If format different
throw new Error('CHK_saveImageToDriveCore_: Cannot read MIME type.'); // → Throw error
}
const mimeType = mimeMatch[1]; // → Image MIME type
if (CHK_DRIVE_CONFIG.ALLOWED_MIME_TYPES.indexOf(mimeType) === -1) { // → Check allowed list
throw new Error('Unsupported image format.'); // → Reject non-jpg/png
}
const blobBytes = Utilities.base64Decode(dataPart); // → Decode base64
const sizeMb = blobBytes.length / (1024 * 1024); // → Convert bytes → MB
if (sizeMb > CHK_DRIVE_CONFIG.MAX_IMAGE_MB) { // → Check size limit
throw new Error('The image file is too large. Maximum ' // → User-facing message
+ CHK_DRIVE_CONFIG.MAX_IMAGE_MB + 'MB.');
}
const now = new Date(); // → Current time
const tz = CHK_DRIVE_CONFIG.TIMEZONE; // → Fixed timezone
const timeLabel = Utilities.formatDate(now, tz, 'HHmmss'); // → Time string
const safeNameHint = fileNameHint // → Clean up filename hint
? String(fileNameHint).replace(/[^0-9A-Za-z_-]+/g, '_') // → Replace special chars
: 'NO_CONTAINER'; // → Fallback if no hint
const ext = (mimeType === 'image/png') ? '.png' : '.jpg'; // → Extension by MIME
const finalFileName = safeNameHint + '_' + timeLabel + ext; // → Final filename
const folder = CHK_getOrCreateDateFolder_(now); // → Get date folder
const blob = Utilities.newBlob(blobBytes, mimeType, finalFileName); // → Create Blob
const file = folder.createFile(blob); // → Create Drive file
return { // → Return result object
id: file.getId(), // → File ID
url: file.getUrl(), // → Web URL
name: file.getName(), // → Actual stored name
folderName: folder.getName() // → Date folder name
};
}How to verify it works: Using the test function introduced later, pass in an actual base64 string. If an image file appears in the date folder on Drive and opens correctly via its URL, DriveApp and Apps Script are wired up correctly.
Linking to the Sheet and Using LockService: Recording Links in the Appointment Sheet
Having a file in Drive alone isn’t enough for operations. You need to attach the photo link to the appropriate row in the Appointment Main Sheet so that later you can open the corresponding photo with just the container number. Since several users can save concurrently, we use LockService to avoid collisions and handle the process of finding the correct row by container number.
- What it does:
- Acquires a lock so that the same save isn’t processed twice at the same time.
- Finds the row in the Appointment Main Sheet that matches the container number.
- Calls
CHK_saveImageToDriveCore_to save the photo to Drive. - Writes the photo URL into the photo URL column in that row, and returns a result object.
- Where to paste: Directly below the core save function.
- What to do after pasting: Adjust the constants to match your actual sheet name and the column index where photo URLs should go.
const CHK_APPT_SHEET_NAME = 'APPT_MAIN'; // -> Appointment sheet name
const CHK_PHOTO_SHEET_NAME = 'CHECKIN_PHOTOS'; // -> Photo log sheet (new in this part)
// Appointment column indices follow the series layout: A date B start time C equipment type
// D door E container F carrier G client H remark I end time J created at K qty
// L pallets M booking ID. **There is no photo column** - that is why we log to a separate sheet.
const CHK_APPT_COL_DATE = 1; // -> A: date
const CHK_APPT_COL_CONTAINER = 5; // -> E: container number
const CHK_APPT_COL_BOOKING_ID = 13; // -> M: booking ID
const CHK_APPT_COL_LAST = 13; // -> columns to read (A-M)
// Photo log headers - one photo per row.
const CHK_PHOTO_HEADERS = ['Logged at', 'Appt date', 'Container', 'Appt row',
'Booking ID', 'Kind', 'File name', 'Photo URL'];
/**
* Prepare the photo log sheet (create it, and add headers when empty).
* Part 1's getOrCreateSheet_() creates the sheet but writes no headers.
*/
function CHK_getPhotoSheet_() { // -> Prepare photo log sheet
const ss = SpreadsheetApp.getActiveSpreadsheet(); // -> Active spreadsheet
let sheet = ss.getSheetByName(CHK_PHOTO_SHEET_NAME); // -> Reuse if present
if (!sheet) { // -> Otherwise
sheet = ss.insertSheet(CHK_PHOTO_SHEET_NAME); // -> create it
} //
if (sheet.getLastRow() === 0) { // -> Still empty
sheet.getRange(1, 1, 1, CHK_PHOTO_HEADERS.length) // -> Row 1
.setValues([CHK_PHOTO_HEADERS]); // -> write headers
sheet.setFrozenRows(1); // -> freeze header
} //
return sheet; // -> Return sheet
}
/**
* Save a check-in photo - **the row number comes from part 1's lookup (data.rowIndex).**
* @param {number} apptRow Row number reported by getApptInfoByCntr()
* @param {string} containerNo Container number (used to re-verify the row)
* @param {string} base64Data data:image/... string
* @param {string} photoType 'BOL' | 'POD' | 'OTHER'
*/
function CHK_saveImageToDrive(apptRow, containerNo, base64Data, photoType) {
const lock = LockService.getScriptLock(); // -> Script lock
lock.waitLock(30000); // -> Wait up to 30s
try {
const rowNo = Number(apptRow); // -> Row number as a number
if (!Number.isInteger(rowNo) || rowNo < 2) { // -> Must be below the header
throw new Error('Invalid appointment row. Run the lookup first.');
}
const cntr = String(containerNo || '').trim().toUpperCase(); // -> Normalize to upper case
if (!cntr) { // -> No container number
throw new Error('Container number is missing.'); // -> Reject
}
const ss = SpreadsheetApp.getActiveSpreadsheet(); // -> Active spreadsheet
const sheet = ss.getSheetByName(CHK_APPT_SHEET_NAME); // -> Appointment sheet
if (!sheet) { // -> Missing sheet
throw new Error('Appointment sheet not found.'); // -> Stop
}
if (rowNo > sheet.getLastRow()) { // -> Beyond the sheet
throw new Error('Appointment row not found. Please look it up again.');
}
// Rows may shift while the screen is open. Re-check that this row really is **today's
// booking for this container** before saving, or the photo lands on the wrong booking.
const row = sheet.getRange(rowNo, 1, 1, CHK_APPT_COL_LAST).getValues()[0];
const rowCntr = String(row[CHK_APPT_COL_CONTAINER - 1] || '').trim().toUpperCase();
if (rowCntr !== cntr) { // -> Different booking
throw new Error('The booking changed. Please look up the container again.');
}
let rowYmd; // -> Booking date on that row
try { // -> Normalize
rowYmd = APPT_ymd_(row[CHK_APPT_COL_DATE - 1]); // -> 'YYYY-MM-DD'
} catch (e) { // -> Unreadable date
throw new Error('Could not read the date on row ' + rowNo + '. Please contact the office.');
}
if (rowYmd !== APPT_ymd_(new Date())) { // -> Not today
throw new Error('That is not today\'s booking. Please look up the container again.');
}
const result = CHK_saveImageToDriveCore_( // -> Save to Drive
base64Data, // -> Image data
cntr // -> File name hint
);
// **Append, never overwrite.** One booking gets BOL, POD and damage photos.
const photoSheet = CHK_getPhotoSheet_(); // -> Photo log sheet
photoSheet.appendRow([ // -> One photo = one row
new Date(), // -> A: logged at
rowYmd, // -> B: appointment date
cntr, // -> C: container
rowNo, // -> D: appointment row
String(row[CHK_APPT_COL_BOOKING_ID - 1] || ''), // -> E: booking ID
String(photoType || 'OTHER').trim(), // -> F: kind
result.name, // -> G: file name
result.url // -> H: photo URL
]);
return { // -> Return to the web app
success: true, // -> Success flag
fileUrl: result.url, // -> File URL
fileId: result.id, // -> File ID
fileName: result.name, // -> File name
row: rowNo // -> Appointment row
};
} catch (e) {
return { // -> Errors come back as objects
success: false, // -> Failure flag
message: e.message || String(e) // -> Error message
};
} finally {
lock.releaseLock(); // -> Release the lock
}
}How to verify it works: From your check-in web app, take a photo and save it, then check the Appointment Main Sheet. If the photo URL appears in the photo URL column for that container’s row, and clicking the link opens the Drive image, Apps Script and DriveApp are working together correctly.
Testing and Error Handling: Verifying Image Upload Behavior
Image upload involves long, tightly structured input data, so in practice it’s helpful to have a dedicated test function to confirm behavior and error messages. This is especially useful when validating the base64 format coming from a frontend written by someone else.
- What it does:
- Uses a test container number and base64 string to call
CHK_saveImageToDrive. - Logs the result so you can check success/failure and error messages.
- Where to paste: Directly below
CHK_saveImageToDrive. - What to do after pasting: Replace
TEST_BASE64with a real base64 string and check the execution log.
function CHK_testSaveImageToDrive() { // -> Test the save function
// Run part 1's lookup first to get **a row number that really is today's booking**.
// Hard-coding a row number makes the test meaningless - you cannot tell what it points at.
const TEST_CONTAINER = 'TEST123456'; // -> Use a container booked today
const TEST_BASE64 = 'data:image/jpeg;base64,AAAA'; // -> Replace with real base64
const found = getApptInfoByCntr(TEST_CONTAINER); // -> Part 1 lookup
if (!found.found) { // -> Nothing booked today
Logger.log('No booking today: ' + found.reason + ' / ' + found.message);
return; // -> Stop here
}
const result = CHK_saveImageToDrive(found.data.rowIndex, // -> Row from the lookup
TEST_CONTAINER, TEST_BASE64, 'BOL'); // -> number, image, kind
Logger.log(JSON.stringify(result)); // -> Log the result
}How to verify it works: In the Apps Script editor, select and run CHK_testSaveImageToDrive, then open the execution log and check whether {"success":true,...} is printed. At the same time, confirm that a date-based folder and image file are created in Drive and that the URL has been written into the row for the test container in the Appointment Main Sheet. If it fails, success:false will be returned along with a message telling you whether the problem is MIME type, file size, or container-number matching, so you can pinpoint what to fix.
Practical Tips: Making Logistics Check-in Photos Useful Long-term
From actually managing BOL/POD photos with Google Drive and Google Sheets in warehouse operations, several non-code habits turned out to be especially important.
First, keep the folder structure as simple as possible. If you go deep with “date → carrier → container,” it becomes hard to explain the rules to new staff, and once people start creating their own subfolders, files become hard to track again. A single date folder plus a filename that includes container number and time was enough to cover most lookup situations.
Second, design your size limit and photo quality together. Documents shot at the dock usually don’t need extremely high resolution. If you cap uploads at around 10 MB in Apps Script and instruct mobile users to shoot at “standard” quality, you can reduce upload errors and improve save speed.
Third, when recording photo links in the sheet, it helps to manage column indices as constants, as shown here. It’s common for the Appointment Main Sheet to grow more columns over time. By designing the script so that you only need to change CHK_APPT_COL_PHOTO_URL instead of editing code everywhere, maintenance is much easier.
Fourth, when several teams share this “Google Apps Script image upload → sheet link” workflow, it’s useful to separate ownership and permissions. Make the script and Drive folder owned by a shared or operations account, and grant view/edit to staff as needed. This simplifies account handover, and when sharing links with outside carriers or customers, you can more easily ensure that access settings comply with your workspace policies.
Lastly, when rolling out a Google Sheets–based logistics check-in photo system, it’s wise to pilot on one or two docks first. Run it for a few weeks in an actual line, reviewing photo quality, upload speed, and claim-handling time. Then tweak the rules before expanding to the whole site; this helps reduce pushback from the field.
Conclusion: Start Today with a Single Test Container
BOL/POD photos taken at check-in are effectively your last line of defense when issues arise later. Whether those photos are scattered across email and messengers, or whether they’re connected in a clean chain like “Appointment Main Sheet container number → photo link → Drive date folder” makes a big difference in how long resolution takes.
You can take a simple step today. Add a Photo URL column to the right side of your existing Appointment Main Sheet, then update CHK_APPT_SHEET_NAME and CHK_APPT_COL_PHOTO_URL in the code above to match your actual sheet structure. Pick a test container, run CHK_testSaveImageToDrive, and see it work end to end. Once you’ve seen the Google Sheets check-in photo → Drive save flow run successfully even once, it becomes much easier to hook it up to your real check-in screen and extend it into a full BOL/POD auto-organization system.
