Turn Your Google Sheets into a Web Service in 5 Minutes!

Replace all UPPERCASE texts with your actual values
YOUR_SHEET_ID • YOUR_SHEET_NAME • YOUR_KEY_NAME (e.g., id, phone, cpf…)

1. Overview

• Web App exposes your Google Sheets spreadsheet as an HTTP endpoint (GET or POST).

• In-memory cache uses an index in a Map for responses in milliseconds.

• Key search query by YOUR_KEY_NAME (e.g., id=123) using index + quick fallback.

• Rebuild & Incremental You control when to rebuild the index (manual or trigger every 1 min).

2. Script-based (anonymized)

/**
 * Web App – Spreadsheet API
 * Replace ALL UPPERCASE placeholders with your real data!
 */

/* ⚙️ CONFIGURATION */
const SHEET_ID = 'YOUR_SHEET_ID';
const SHEET_NAME  = 'YOUR_SHEET_NAME';

/* 🗺️ Map only the columns you want to expose (A=1, B=2, …) */
const COL = {
  key     : 1,   // YOUR_KEY_NAME (search key)
  field01 : 2,
  field02 : 3,
  field03 : 4
};
const NUM_COLS      = Object.keys(COL).length;
const TTL_CACHE_MS  = 15 * 60 * 1000;   // 15 min
const CHUNK_ROWS    = 8000;             // read in blocks

/* 🛢️ Cache */
let cache = { map:new Map(), last:0, building:false };

/* 🌐 ENDPOINTS */
function doGet(e){ return handle(e); }
function doPost(e){
  if (e.postData && e.postData.type === 'application/json')
    Object.assign(e.parameter, JSON.parse(e.postData.contents||'{}'));
  return handle(e);
}

/* 🧠 MAIN LOGIC */
function handle(e){
  const id = (e.parameter['YOUR_KEY_NAME'] || '').trim();
  if (!id) return out({found:false,error:`Missing 'YOUR_KEY_NAME' parameter`});

  try{
    if (Date.now() - cache.last > TTL_CACHE_MS) buildIndex();
    const hit = cache.map.get(id);
    if (hit) return out({found:true, ...hit});

    const obj = searchSheet(id);
    return out(obj ? {found:true, ...obj} : {found:false});
  }catch(err){
    return out({found:false,error:err.toString()});
  }
}

/* 🔍 Direct search (fallback) */
function searchSheet(id){
  const sh  = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
  const rng = sh.getRange(1, COL.key, sh.getLastRow(), 1)
                .createTextFinder(id).matchEntireCell(true).findNext();
  if (!rng) return null;

  const vals = sh.getRange(rng.getRow(), 1, 1, NUM_COLS).getValues()[0];
  const obj  = rowToObj(vals);
  cache.map.set(id, obj);          // save to cache
  return obj;
}

/* 🔄 Build or refresh the full index */
function buildIndex(){
  if (cache.building) return;
  cache.building = true;
  try{
    const sh   = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
    const rows = sh.getLastRow() - 1;
    const map  = new Map();

    for (let i=2;i<=rows+1;i+=CHUNK_ROWS){
      const end = Math.min(i+CHUNK_ROWS-1,rows+1);
      sh.getRange(i,1,end-i+1,NUM_COLS).getValues().forEach(r=>{
        const id = (r[COL.key-1]||'').toString().trim();
        if (id) map.set(id, rowToObj(r));
      });
    }
    cache = { map, last:Date.now(), building:false };
  }finally{ cache.building=false; }
}

/* 🧩 Helpers */
function rowToObj(r){
  const o={}; for (const k in COL) o[k]=r[COL[k]-1]; return o;
}
const out = o=>ContentService.createTextOutput(JSON.stringify(o))
                              .setMimeType(ContentService.MimeType.JSON);

/* ⏰ Create incremental trigger (every 1 min) */
function createIncrementalTrigger(){
  ScriptApp.newTrigger('buildIndex')
           .timeBased().everyMinutes(1).create();
}

3. Essential Step-by-Step

A. Configure & paste the script

1. Open script.google.com → New Project.

2. Paste the above template.

3. Replace ALL placeholders YOUR_….

B. Build the first index manually

1. In the editor → Run buildIndex() once.

2. This loads all rows into memory (faster for the first request).

💡 Quick tip
If you prefer, just call the API with &rebuild=true right after deployment – same effect.

C. Create the incremental trigger

1. Once the initial index is built, select the function createIncrementalTrigger from the dropdown menu.

2. Click Run → authorize.

3. A 1-minute trigger will keep the cache synchronized effortlessly.

D. Publish and generate the URL

1. Deploy → New deployment → Web app.

2. Execute as: Me • Who has access: Anyone (or anyone with the link).

3. Deploy → Authorize → Copy URL.

   https://script.google.com/macros/s/YOUR_DEPLOY_ID/exec
   
   

4. Quick Tests

5. Common Problems & Solutions

🔒 Quick Security

• Web App runs with your credentials → clients only see JSON.

• To limit access, change to Only myself and require a token / API Key.

Done!

1. Build the first index (buildIndex or rebuild=true).

2. Create the incremental trigger.

3. Publish and use!

Any questions, contact us. 😉