Multi value search

Hello Andres Diaz , 

Thanks for the question!

A while back, I started experimenting with Google Sheets and Apps Script using ChatGPT, and this is a great example of how I can put that learning to good use.

If you have your email addresses stored somewhere else, you can use Google Sheets to search Sugar using the filter endpoint for a specific email. If a match is found, it will populate the corresponding column with the data. If no match is found, it will simply write “no record found.”

Here’s what the final result would look like:

If you want to try it out these were the steps I used on my side to search on the Contacts module. 

1. Create  Google Sheet and name the tab “Emails”.

2. Paste your list of email addresses into column A, starting from A2.

3. Go to Extensions > Apps Script and paste the script that is bellow
   
   
   

   Fullscreen

   1

   2

   3

   4

   5

   6

   7

   8

   9

   10

   11

   12

   13

   14

   15

   16

   17

   18

   19

   20

   21

   function getSugarToken() {

   const url = 'https://yourdomain.sugarcrm.com/rest/v11_25/oauth2/token';

   const payload = {

   grant_type: 'password',

   client_id: 'sugar',

   client_secret: '',

   username: 'username',

   password: 'password',

   platform: 'base'

   };

   const options = {

   method: 'post',

   contentType: 'application/json',

   payload: JSON.stringify(payload)

   };

   const response = UrlFetchApp.fetch(url, options);

   const json = JSON.parse(response.getContentText());

   return json.access_token;

   }

   XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

     function getSugarToken() {
     const url = 'https://yourdomain.sugarcrm.com/rest/v11_25/oauth2/token';
     const payload = {
       grant_type: 'password',
       client_id: 'sugar',
       client_secret: '',
       username: 'username',
       password: 'password',
       platform: 'base'
     };
   
     const options = {
       method: 'post',
       contentType: 'application/json',
       payload: JSON.stringify(payload)
     };
   
     const response = UrlFetchApp.fetch(url, options);
     const json = JSON.parse(response.getContentText());
     return json.access_token;
   }
   
   function checkSugarContacts() {
     const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emails");
     const url = "https://yourdomain.sugarcrm.com/rest/v11_25/Contacts/filter";
     const baseSugarUrl = "https://yourdomain.sugarcrm.com/#Contacts/";
     const token = getSugarToken();
   
     const range = sheet.getRange("A2:A" + sheet.getLastRow());
     const emails = range.getValues();
   
     emails.forEach((row, i) => {
       const email = row[0];
       if (!email) return;
   
       const options = {
         method: "get",
         headers: {
           "oauth-token": token
         },
         muteHttpExceptions: true
       };
   
       const filterUrl = `${url}?filter[0][email][$equals]=${encodeURIComponent(email)}`;
   
       try {
         const response = UrlFetchApp.fetch(filterUrl, options);
         const json = JSON.parse(response.getContentText());
   
         if (json.records && json.records.length > 0) {
           sheet.getRange(i + 2, 2).setValue(baseSugarUrl + json.records[0].id);
         } else {
           sheet.getRange(i + 2, 2).setValue("no record found");
         }
       } catch (e) {
         sheet.getRange(i + 2, 2).setValue("error");
       }
     });
   
   }
   

   
   
   
    

4. Replace:

   • "yourdomain.sugarcrm.com" with your actual SugarCRM domain

   • "username" and "password" with valid Sugar credentials

5. Save and click the run Icon

This will populate the Column B with the results of each filter endpoint. 

Let me know if you managed to have it working and if it helps at all on your use case. 

Cheers, 

André