Shared chat

Create Google Sheets PWA

i need to turn this google sheet into pwa app, i will need to upload and display two photos, vin photo and vehicle photo. service column will need a drop down for 5 services. Basic recon, price $30. Full LMC $130. LMC Detail SUV $170. Superior recon $60. Superior recon SUV $85

Great use case — you can do this with Google Apps Script as the backend (writes rows to your Sheet, saves photos to Drive) and a simple PWA frontend (installable, works on a phone camera). Here's a complete working setup.

Architecture

Google Sheet = your database (columns: Timestamp, VIN, Service, Price, VIN Photo URL, Vehicle Photo URL)
Apps Script Web App = API that receives form data + photos, saves photos to a Drive folder, appends a row
PWA = HTML form with camera/photo inputs, service dropdown with auto-price, manifest + service worker so it installs like an app

1. Apps Script backend (`Code.gs`)

In your Sheet: Extensions → Apps Script, paste this:

javascript

const SHEET_NAME = 'Sheet1';const FOLDER_ID = 'YOUR_DRIVE_FOLDER_ID'; // create a Drive folder for photos function doPost(e) {  const data = JSON.parse(e.postData.contents);  const folder = DriveApp.getFolderById(FOLDER_ID);   const vinUrl = saveImage(folder, data.vinPhoto, `VIN_${data.vin}`);  const vehicleUrl = saveImage(folder, data.vehiclePhoto, `VEHICLE_${data.vin}`);   const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);  sheet.appendRow([new Date(), data.vin, data.service, data.price, vinUrl, vehicleUrl]);   return ContentService.createTextOutput(JSON.stringify({ ok: true }))    .setMimeType(ContentService.MimeType.JSON);} function saveImage(folder, base64, name) {  if (!base64) return '';  const bytes = Utilities.base64Decode(base64.split(',')[1]);  const blob = Utilities.newBlob(bytes, 'image/jpeg', name + '.jpg');  const file = folder.createFile(blob);  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);  return `https://drive.google.com/uc?id=${file.getId()}`;} // Optional: fetch rows so the app can display entriesfunction doGet() {  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);  const rows = sheet.getDataRange().getValues();  return ContentService.createTextOutput(JSON.stringify(rows))    .setMimeType(ContentService.MimeType.JSON);}

Then: Deploy → New deployment → Web app → Execute as Me, access Anyone. Copy the web app URL.

2. PWA frontend (`index.html`)

Host this on GitHub Pages, Netlify, or Vercel (free — PWAs need HTTPS):

html

<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1"><title>Recon Tracker</title><link rel="manifest" href="manifest.json"><meta name="theme-color" content="#1a73e8"><style>  body { font-family: system-ui; max-width: 480px; margin: auto; padding: 16px; }  label { display: block; margin-top: 12px; font-weight: 600; }  input, select, button { width: 100%; padding: 10px; margin-top: 4px; box-sizing: border-box; }  img.preview { max-width: 100%; margin-top: 8px; border-radius: 8px; display: none; }  button { background: #1a73e8; color: #fff; border: 0; border-radius: 8px; margin-top: 16px; font-size: 16px; }  #price { font-size: 20px; font-weight: bold; margin-top: 8px; }</style></head><body><h2>Vehicle Recon Entry</h2> <label>VIN</label><input id="vin" placeholder="Enter VIN"> <label>Service</label><select id="service">  <option value="" data-price="0">Select service…</option>  <option data-price="30">Basic Recon — $30</option>  <option data-price="130">Full LMC — $130</option>  <option data-price="170">LMC Detail SUV — $170</option>  <option data-price="60">Superior Recon — $60</option>  <option data-price="85">Superior Recon SUV — $85</option></select><div id="price"></div> <label>VIN Photo</label><input type="file" id="vinPhoto" accept="image/*" capture="environment"><img class="preview" id="vinPreview"> <label>Vehicle Photo</label><input type="file" id="vehiclePhoto" accept="image/*" capture="environment"><img class="preview" id="vehiclePreview"> <button id="submit">Save Entry</button> <script>const API_URL = 'YOUR_APPS_SCRIPT_WEB_APP_URL'; const serviceEl = document.getElementById('service');serviceEl.onchange = () => {  const p = serviceEl.selectedOptions[0].dataset.price;  document.getElementById('price').textContent = p > 0 ? `Price: $${p}` : '';}; function readAsCompressedBase64(file, previewId) {  return new Promise(resolve => {    const img = new Image();    img.onload = () => {      const canvas = document.createElement('canvas');      const scale = Math.min(1, 1280 / img.width);      canvas.width = img.width * scale;      canvas.height = img.height * scale;      canvas.getContext('2d').drawImage(img, 0, 0, canvas.width, canvas.height);      const dataUrl = canvas.toDataURL('image/jpeg', 0.7);      const prev = document.getElementById(previewId);      prev.src = dataUrl; prev.style.display = 'block';      resolve(dataUrl);    };    img.src = URL.createObjectURL(file);  });} let vinPhotoData = '', vehiclePhotoData = '';document.getElementById('vinPhoto').onchange = async e =>  vinPhotoData = await readAsCompressedBase64(e.target.files[0], 'vinPreview');document.getElementById('vehiclePhoto').onchange = async e =>  vehiclePhotoData = await readAsCompressedBase64(e.target.files[0], 'vehiclePreview'); document.getElementById('submit').onclick = async () => {  const vin = document.getElementById('vin').value.trim();  const opt = serviceEl.selectedOptions[0];  if (!vin || !opt.dataset.price || opt.dataset.price === '0') {    alert('Enter VIN and select a service'); return;  }  const btn = document.getElementById('submit');  btn.disabled = true; btn.textContent = 'Saving…';  await fetch(API_URL, {    method: 'POST',    body: JSON.stringify({      vin,      service: opt.textContent.split('—')[0].trim(),      price: opt.dataset.price,      vinPhoto: vinPhotoData,      vehiclePhoto: vehiclePhotoData    })  });  btn.disabled = false; btn.textContent = 'Save Entry';  alert('Saved!');  location.reload();}; if ('serviceWorker' in navigator) navigator.serviceWorker.register('sw.js');</script></body></html>

3. PWA files

manifest.json:

json

{  "name": "Recon Tracker",  "short_name": "Recon",  "start_url": ".",  "display": "standalone",  "background_color": "#ffffff",  "theme_color": "#1a73e8",  "icons": [    { "src": "icon-192.png", "sizes": "192x192", "type": "image/png" },    { "src": "icon-512.png", "sizes": "512x512", "type": "image/png" }  ]}

sw.js:

javascript

const CACHE = 'recon-v1';self.addEventListener('install', e => {  e.waitUntil(caches.open(CACHE).then(c => c.addAll(['.', 'index.html', 'manifest.json'])));});self.addEventListener('fetch', e => {  e.respondWith(caches.match(e.request).then(r => r || fetch(e.request)));});

Setup checklist

Create a Drive folder for photos → copy its ID into FOLDER_ID
Paste Code.gs into Apps Script → deploy as web app → copy URL into API_URL
Upload the 3 frontend files + two icon PNGs to GitHub Pages/Netlify
Open on your phone → "Add to Home Screen" → it installs as an app

Notes:

capture="environment" opens the rear camera directly on phones — perfect for VIN shots.
Photos are compressed to ~1280px JPEG before upload to keep things fast.
Want offline queueing (save entries with no signal, sync later)? That's an IndexedDB addition to the service worker — let me know and I'll extend it.

If you share your sheet's exact column headers, I'll adjust the appendRow order to match.

Claude Fable 53.5k tok116 tok/sTTFT 5.2s35.7s

how would I display actual photos, reduced size image instead of links?

You have two places to do this — in the Google Sheet itself and in the app's list view. Here's both.

1. Show thumbnails in the Google Sheet

The trick: drive.google.com/uc?id=... links often don't render in Sheets' IMAGE() formula. Use Drive's thumbnail endpoint instead — it also gives you the reduced size for free:

https://drive.google.com/thumbnail?id=FILE_ID&sz=w200

Update saveImage() in your Apps Script to write an IMAGE() formula instead of a bare URL:

javascript

function saveImage(folder, base64, name) {  if (!base64) return '';  const bytes = Utilities.base64Decode(base64.split(',')[1]);  const blob = Utilities.newBlob(bytes, 'image/jpeg', name + '.jpg');  const file = folder.createFile(blob);  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);  return file.getId(); // return just the ID now}

Then in doPost, write the formulas with setFormula instead of plain appendRow:

javascript

function doPost(e) {  const data = JSON.parse(e.postData.contents);  const folder = DriveApp.getFolderById(FOLDER_ID);   const vinId = saveImage(folder, data.vinPhoto, `VIN_${data.vin}`);  const vehicleId = saveImage(folder, data.vehiclePhoto, `VEHICLE_${data.vin}`);   const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);  const row = sheet.getLastRow() + 1;   sheet.getRange(row, 1, 1, 4).setValues([[new Date(), data.vin, data.service, data.price]]);   if (vinId) sheet.getRange(row, 5).setFormula(    `=IMAGE("https://drive.google.com/thumbnail?id=${vinId}&sz=w200")`);  if (vehicleId) sheet.getRange(row, 6).setFormula(    `=IMAGE("https://drive.google.com/thumbnail?id=${vehicleId}&sz=w200")`);   // optional: store raw IDs in hidden columns G/H so the app can still fetch them  sheet.getRange(row, 7, 1, 2).setValues([[vinId, vehicleId]]);   // make the row tall enough to see the thumbnails  sheet.setRowHeight(row, 80);   return ContentService.createTextOutput(JSON.stringify({ ok: true }))    .setMimeType(ContentService.MimeType.JSON);}

Tips:

sz=w200 = 200px wide. Use w400 for bigger, w100 for smaller.
IMAGE(url) defaults to "fit to cell," so the row height / column width controls display size. IMAGE(url, 4, 80, 80) forces an exact 80×80 px if you prefer.
Keeping the raw file IDs in columns G/H means your app doesn't have to parse formulas.

2. Show thumbnails in the PWA (list/gallery view)

If you add a list screen that reads from doGet(), render the IDs as <img> tags using the same thumbnail URL, with a click-through to the full image:

javascript

async function loadEntries() {  const rows = await (await fetch(API_URL)).json();  const list = document.getElementById('entries');  list.innerHTML = rows.slice(1).map(r => {    const [ts, vin, service, price, , , vinId, vehicleId] = r;    const thumb = id => id      ? `<a href="https://drive.google.com/file/d/${id}/view" target="_blank">           <img src="https://drive.google.com/thumbnail?id=${id}&sz=w200" loading="lazy">         </a>`      : '';    return `<div class="card">      <strong>${vin}</strong> — ${service} ($${price})      <div class="thumbs">${thumb(vinId)}${thumb(vehicleId)}</div>    </div>`;  }).join('');}

With a little CSS:

css

.card { border: 1px solid #ddd; border-radius: 8px; padding: 10px; margin-top: 10px; }.thumbs img { width: 120px; height: 90px; object-fit: cover; border-radius: 6px; margin-right: 8px; }

loading="lazy" keeps the list fast when you have many rows.
Tapping a thumbnail opens the full-resolution photo in Drive.
Photos must be shared "Anyone with link" (your setSharing call already does this) or the thumbnails will show as broken images.

One caveat: if you have existing rows with the old uc?id= URLs, you can convert them once with a formula like:

=IMAGE(SUBSTITUTE(E2, "uc?id=", "thumbnail?id=") & "&sz=w200")

Want me to add the full gallery/list screen to the PWA so you can browse and search past entries by VIN?

Claude Fable 52.0k tok115 tok/sTTFT 6.7s23.9s

ok i have this working, i was able to take and upload pictures, input vin number and select service i do not see the results on the google sheet though. how would i generate the results to the spreadsheet, or what am i missing ?

Most likely one of these is missing:

Your Apps Script is saving photos to Drive but not appending the row
SHEET_NAME does not match your actual Google Sheet tab name
The script is not connected to the correct spreadsheet
You edited Apps Script but did not redeploy a new Web App version
The PWA is using the wrong Apps Script URL
The request is failing silently

The safest fix is to use the Spreadsheet ID directly instead of relying on getActiveSpreadsheet().

Use this Apps Script backend

In Apps Script, replace your backend with this version.

Change these 3 values:

javascript

const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID';const SHEET_NAME = 'Sheet1';const FOLDER_ID = 'YOUR_DRIVE_FOLDER_ID';

Full code:

javascript

const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID';const SHEET_NAME = 'Sheet1';const FOLDER_ID = 'YOUR_DRIVE_FOLDER_ID'; const SERVICES = {  'Basic Recon': 30,  'Full LMC': 130,  'LMC Detail SUV': 170,  'Superior Recon': 60,  'Superior Recon SUV': 85}; function doPost(e) {  try {    const data = JSON.parse(e.postData.contents);     const ss = SpreadsheetApp.openById(SPREADSHEET_ID);    const sheet = ss.getSheetByName(SHEET_NAME);     if (!sheet) {      throw new Error(`Sheet tab not found: ${SHEET_NAME}`);    }     setupHeadersIfNeeded(sheet);     const folder = DriveApp.getFolderById(FOLDER_ID);     const vin = data.vin || '';    const service = data.service || '';    const price = SERVICES[service] || data.price || '';     const vinPhotoId = saveImage(folder, data.vinPhoto, `VIN_${vin}_${Date.now()}`);    const vehiclePhotoId = saveImage(folder, data.vehiclePhoto, `VEHICLE_${vin}_${Date.now()}`);     const row = sheet.getLastRow() + 1;     sheet.getRange(row, 1, 1, 8).setValues([[      new Date(),      vin,      service,      price,      vinPhotoId,      vehiclePhotoId,      '',      ''    ]]);     if (vinPhotoId) {      sheet.getRange(row, 7).setFormula(        `=IMAGE("https://drive.google.com/thumbnail?id=${vinPhotoId}&sz=w200")`      );    }     if (vehiclePhotoId) {      sheet.getRange(row, 8).setFormula(        `=IMAGE("https://drive.google.com/thumbnail?id=${vehiclePhotoId}&sz=w200")`      );    }     sheet.setRowHeight(row, 100);    sheet.setColumnWidth(7, 120);    sheet.setColumnWidth(8, 120);     return jsonResponse({      ok: true,      message: 'Saved to sheet',      row,      vin,      service,      price    });   } catch (err) {    return jsonResponse({      ok: false,      error: err.message    });  }} function saveImage(folder, base64, name) {  if (!base64) return '';   const contentType = base64.substring(    base64.indexOf(':') + 1,    base64.indexOf(';')  );   const bytes = Utilities.base64Decode(base64.split(',')[1]);  const blob = Utilities.newBlob(bytes, contentType, name + '.jpg');   const file = folder.createFile(blob);  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);   return file.getId();} function setupHeadersIfNeeded(sheet) {  if (sheet.getLastRow() === 0) {    sheet.appendRow([      'Timestamp',      'VIN',      'Service',      'Price',      'VIN Photo File ID',      'Vehicle Photo File ID',      'VIN Photo',      'Vehicle Photo'    ]);  }} function doGet() {  return jsonResponse({    ok: true,    message: 'Apps Script web app is running'  });} function jsonResponse(obj) {  return ContentService    .createTextOutput(JSON.stringify(obj))    .setMimeType(ContentService.MimeType.JSON);}

Important: get the correct Spreadsheet ID

Your Google Sheet URL looks like this:

https://docs.google.com/spreadsheets/d/SPREADSHEET_ID_HERE/edit

Copy only the long ID between /d/ and /edit.

Example:

javascript

const SPREADSHEET_ID = '1abcDEFghiJKLmnopQRSTUVWXYZ123456789';

Important: `SHEET_NAME` means the bottom tab name

This is not the file name.

If your bottom tab says:

Recon

Then use:

javascript

const SHEET_NAME = 'Recon';

If it says:

Sheet1

Then use:

javascript

const SHEET_NAME = 'Sheet1';

Your frontend submit code should look like this

Make sure your PWA is sending data to the Apps Script URL.

javascript

document.getElementById('submit').onclick = async () => {  const vin = document.getElementById('vin').value.trim();  const serviceEl = document.getElementById('service');  const selectedOption = serviceEl.selectedOptions[0];   const service = selectedOption.value;  const price = selectedOption.dataset.price;   if (!vin) {    alert('Enter VIN');    return;  }   if (!service) {    alert('Select a service');    return;  }   if (!vinPhotoData) {    alert('Take VIN photo');    return;  }   if (!vehiclePhotoData) {    alert('Take vehicle photo');    return;  }   const btn = document.getElementById('submit');  btn.disabled = true;  btn.textContent = 'Saving...';   try {    const response = await fetch(API_URL, {      method: 'POST',      body: JSON.stringify({        vin,        service,        price,        vinPhoto: vinPhotoData,        vehiclePhoto: vehiclePhotoData      })    });     const result = await response.json();     if (!result.ok) {      throw new Error(result.error || 'Unknown error');    }     alert(`Saved to Google Sheet. Row ${result.row}`);     document.getElementById('vin').value = '';    serviceEl.value = '';    document.getElementById('price').textContent = '';    document.getElementById('vinPhoto').value = '';    document.getElementById('vehiclePhoto').value = '';    document.getElementById('vinPreview').style.display = 'none';    document.getElementById('vehiclePreview').style.display = 'none';     vinPhotoData = '';    vehiclePhotoData = '';   } catch (err) {    alert('Save failed: ' + err.message);  }   btn.disabled = false;  btn.textContent = 'Save Entry';};

Your service dropdown should use clean value names:

html

<select id="service">  <option value="" data-price="0">Select service...</option>  <option value="Basic Recon" data-price="30">Basic Recon — $30</option>  <option value="Full LMC" data-price="130">Full LMC — $130</option>  <option value="LMC Detail SUV" data-price="170">LMC Detail SUV — $170</option>  <option value="Superior Recon" data-price="60">Superior Recon — $60</option>  <option value="Superior Recon SUV" data-price="85">Superior Recon SUV — $85</option></select>

Critical step: redeploy after editing Apps Script

After changing the Apps Script code:

Click Deploy
Click Manage deployments
Click the pencil/edit icon
Under Version, choose New version
Click Deploy
Copy the Web App URL again if needed

Your PWA should use the URL ending in:

/exec

Not:

/dev

Example in your PWA:

javascript

const API_URL = 'https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec';

How to check what is failing

In Apps Script:

Open your Apps Script project
Click Executions on the left
Submit a test entry from the PWA
Refresh Executions
Open the latest run

If it failed, you will usually see one of these:

Wrong sheet tab name

Sheet tab not found: Sheet1

Fix:

javascript

const SHEET_NAME = 'Your Actual Tab Name';

Wrong spreadsheet ID

Exception: Unexpected error while getting the method or property openById

Fix SPREADSHEET_ID.

Wrong Drive folder ID

Exception: Unexpected error while getting the method or property getFolderById

Fix FOLDER_ID.

Permission issue

Redeploy the Web App as:

Execute as: Me
Who has access: Anyone

What should appear in the Sheet

The Sheet should generate rows like this:

Timestamp	VIN	Service	Price	VIN Photo File ID	Vehicle Photo File ID	VIN Photo	Vehicle Photo
date/time	123456789	Basic Recon	30	file ID	file ID	thumbnail	thumbnail

Columns G and H will show actual reduced-size images, not just links.

Most likely issue in your case

If photos are uploading but the Sheet is blank, the most likely problem is this line from the older code:

javascript

SpreadsheetApp.getActiveSpreadsheet()

That can fail or point to nothing if the script is not properly bound to the Sheet.

Use this instead:

javascript

SpreadsheetApp.openById(SPREADSHEET_ID)

That forces Apps Script to write to the exact Google Sheet you want.

GPT-5.52.7k tok106 tok/sTTFT 5.4s30.9s

This is a shared TryAI chat. Sign in to start your own conversation.

Architecture

1. Apps Script backend (Code.gs)

2. PWA frontend (index.html)

3. PWA files

Setup checklist

1. Show thumbnails in the Google Sheet

2. Show thumbnails in the PWA (list/gallery view)

Use this Apps Script backend

Important: get the correct Spreadsheet ID

Important: SHEET_NAME means the bottom tab name

Your frontend submit code should look like this

Critical step: redeploy after editing Apps Script

How to check what is failing

Wrong sheet tab name

Wrong spreadsheet ID

Wrong Drive folder ID

Permission issue

What should appear in the Sheet

Most likely issue in your case

1. Apps Script backend (`Code.gs`)

2. PWA frontend (`index.html`)

Important: `SHEET_NAME` means the bottom tab name