Send Google Sheets rows to GuardenerAI.

Step by step

1. Store the key in Script Properties

   In the Apps Script editor open Project Settings, then Script Properties, and add a property named GUARDENERAI_KEY with your key as the value. Keep it out of the code itself. Anyone with edit access to the script can read it, so limit who can edit.

2. Paste the script

   Replace the contents of Code.gs with the script below and change YOUR-ORG to your workspace. For each row with an empty Sent cell it searches for the lead by email, updates the match or creates a new lead, then fills in the id and the time. A row that fails gets the error in its Sent cell, so it is not retried until you clear it. The short pause between rows keeps it well under the API’s rate limit.

   Copy

   const BASE = 'https://YOUR-ORG.guardenerai.com/api/leads';
   
   function syncLeads() {
     const key = PropertiesService.getScriptProperties().getProperty('GUARDENERAI_KEY');
     const sheet = SpreadsheetApp.getActive().getSheetByName('Leads');
     const rows = sheet.getDataRange().getValues();
     for (let i = 1; i < rows.length; i++) {
       const [first, last, email, phone, company, notes, , sent] = rows[i];
       if (sent || !email) continue;
       const lead = clean({
         firstName: first, lastName: last,
         email: String(email).trim().toLowerCase(),
         phone: phone, company: company, message: notes,
         sourceMedium: 'google sheet'
       });
       try {
         const found = call('get', BASE + '?filter=email:equals:' + encodeURIComponent(lead.email), key);
         const saved = found.totalElements > 0
           ? call('put', BASE + '/' + found.content[0].id, key, Object.assign({}, found.content[0], lead))
           : call('post', BASE, key, lead);
         sheet.getRange(i + 1, 7, 1, 2).setValues([[saved.id, new Date()]]);
       } catch (e) {
         sheet.getRange(i + 1, 8).setValue('Error: ' + e.message);
       }
       Utilities.sleep(300);
     }
   }
   
   function call(method, url, key, body) {
     const opts = { method: method, headers: { 'X-Api-Key': key }, muteHttpExceptions: true };
     if (body) { opts.contentType = 'application/json'; opts.payload = JSON.stringify(body); }
     const res = UrlFetchApp.fetch(url, opts);
     if (res.getResponseCode() >= 300) throw new Error(res.getResponseCode() + ' ' + res.getContentText());
     return JSON.parse(res.getContentText());
   }
   
   function clean(obj) {
     Object.keys(obj).forEach(function (k) { if (obj[k] === '' || obj[k] == null) delete obj[k]; });
     return obj;
   }

3. Run it once by hand

   Choose syncLeads and Run. Google asks you to authorise the script to reach external services; approve it. Check that the test rows now show an id, and that the leads appear in GuardenerAI.

4. Put it on a timer

   Open Triggers, choose Add Trigger, pick syncLeads with a Time-driven event source, set how often it runs, and save. Use a timer rather than an on-edit trigger: rows added by other scripts, forms tools or APIs do not fire edit triggers, and a timer catches every row.