Free Google Sheet AI Job Note Script

/**
 * FieldCue missed-call AI job note script
 *
 * What it does:
 * - Runs when a Google Form response lands in the linked Google Sheet
 * - Reads the submitted row
 * - Sends service, urgency, location, and notes to Gemini
 * - Writes back AI job note, priority, next step, and callback script
 *
 * Before using:
 * - Add these output columns to your Sheet:
 *   AI job note
 *   AI priority
 *   AI next step
 *   Callback script
 *
 * - Add your Gemini API key in Apps Script:
 *   Project Settings -> Script Properties
 *   Property: GEMINI_API_KEY
 *   Value: your API key
 */

const CONFIG = {
  model: "gemini-2.5-flash",

  // Change these if your Google Form question names are different.
  inputHeaders: {
    service: "What do you need help with?",
    urgency: "How urgent is it?",
    location: "Where is the job?",
    notes: "Anything else we should know?"
  },

  // These columns must exist in your Sheet.
  outputHeaders: {
    jobNote: "AI job note",
    priority: "AI priority",
    nextStep: "AI next step",
    callbackScript: "Callback script"
  }
};

/**
 * Main function. Set this up as an installable trigger:
 * Event source: From spreadsheet
 * Event type: On form submit
 */
function onFormSubmit(e) {
  const sheet = e.range.getSheet();
  const rowNumber = e.range.getRow();
  processRow_(sheet, rowNumber);
}

/**
 * Optional test function.
 * Use this after you submit one test form response.
 * It processes the last row in the active Sheet.
 */
function testLastRow() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const rowNumber = sheet.getLastRow();
  processRow_(sheet, rowNumber);
}

function processRow_(sheet, rowNumber) {
  const lastColumn = sheet.getLastColumn();
  const headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  const row = sheet.getRange(rowNumber, 1, 1, lastColumn).getValues()[0];

  const values = {
    service: getValueByHeader_(headers, row, CONFIG.inputHeaders.service),
    urgency: getValueByHeader_(headers, row, CONFIG.inputHeaders.urgency),
    location: getValueByHeader_(headers, row, CONFIG.inputHeaders.location),
    notes: getValueByHeader_(headers, row, CONFIG.inputHeaders.notes)
  };

  const aiResult = createJobNote_(values);

  writeValueByHeader_(sheet, headers, rowNumber, CONFIG.outputHeaders.jobNote, aiResult.job_note);
  writeValueByHeader_(sheet, headers, rowNumber, CONFIG.outputHeaders.priority, aiResult.priority);
  writeValueByHeader_(sheet, headers, rowNumber, CONFIG.outputHeaders.nextStep, aiResult.next_step);
  writeValueByHeader_(sheet, headers, rowNumber, CONFIG.outputHeaders.callbackScript, aiResult.callback_script);
}

function createJobNote_(values) {
  const apiKey = PropertiesService.getScriptProperties().getProperty("GEMINI_API_KEY");

  if (!apiKey) {
    throw new Error("Missing GEMINI_API_KEY in Script Properties.");
  }

  const prompt = `
Read this service request and create a short job note for a home-service business.

Return ONLY valid JSON with these keys:
{
  "job_note": "",
  "priority": "High | Medium | Low",
  "next_step": "",
  "callback_script": ""
}

Rules:
- Keep the job note practical and under 40 words.
- Do not promise pricing or availability.
- If urgency is today or emergency, priority should usually be High.
- Callback script should sound natural and short.
- Do not include full personal details unless they are necessary.

Service: ${values.service || ""}
Urgency: ${values.urgency || ""}
Location: ${values.location || ""}
Notes: ${values.notes || ""}
`;

  const url = `https://generativelanguage.googleapis.com/v1beta/models/${CONFIG.model}:generateContent?key=${apiKey}`;

  const payload = {
    contents: [
      {
        parts: [
          { text: prompt }
        ]
      }
    ],
    generationConfig: {
      temperature: 0.2,
      responseMimeType: "application/json"
    }
  };

  const response = UrlFetchApp.fetch(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  });

  const status = response.getResponseCode();
  const body = response.getContentText();

  if (status < 200 || status >= 300) {
    throw new Error(`Gemini API error ${status}: ${body}`);
  }

  const parsed = JSON.parse(body);
  const text = parsed.candidates &&
    parsed.candidates[0] &&
    parsed.candidates[0].content &&
    parsed.candidates[0].content.parts &&
    parsed.candidates[0].content.parts[0] &&
    parsed.candidates[0].content.parts[0].text;

  if (!text) {
    throw new Error(`No AI text returned: ${body}`);
  }

  return JSON.parse(text);
}

function getValueByHeader_(headers, row, headerName) {
  const index = headers.indexOf(headerName);
  return index === -1 ? "" : row[index];
}

function writeValueByHeader_(sheet, headers, rowNumber, headerName, value) {
  const index = headers.indexOf(headerName);

  if (index === -1) {
    throw new Error(`Missing output column: ${headerName}`);
  }

  sheet.getRange(rowNumber, index + 1).setValue(value || "");
}

inputHeaders: {
  service: "What do you need help with?",
  urgency: "How urgent is it?",
  location: "Where is the job?",
  notes: "Anything else we should know?"
}

model: "gemini-2.5-flash"