I’m trying to build a simple CRM for my small business with as many proceses automated as possible. I’m building it in Google Sheets. I have two problems at this time.

I want the system to send me an email when a new form is filled out by a potential client

I want to send an welcome email, when I a client is converted.

The process is like this:

• Client fills out form
• data goes into spreadsheet
• Data is imported into the CRM
• email is sent to me to let me know I need to take action (this part is not working)
• I call the client and try to make the sale
• client converts and I change a dropdown menu to “Konverteret”
• an automated email I sent to the costumer who then goes through the sign-up proces (this step is not working).

I’ve tried to edit these code snippets that I’ve found on YouTube. They are almost what I need, but not quite.

Problem 1, this actually works, but it sends me a ton of emails. I only need one on a new entry.

function sendEmailOnNewEntry() {
  var sheetName = "CRM";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  
  // Get the range of cells containing data in the sheet
  var range = sheet.getDataRange();
  
  // Get the values for all cells in the range
  var data = range.getValues();
  
  // Loop through each row in the sheet
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var activity = row[1]; // Assuming activity is in Column B
    var team = row[2]; // Assuming team is in column C
    var status = row[3]; // Assuming status is in column D
    var emailSent = row[4]; // Assuming emailSent is in column E
    
  
  
      // Send the email
     MailApp.sendEmail("[email protected]","Spørgeskema udfyldt","Gå ind og tjek CRM");
   
    }
  }

Problem 2, this just doesn’t work at all:

function sendMailEdit(i){
  if (i.range.columnStart != 9 || e.value != "Konverteret") return;
  const rData = i.source.getActiveSheet().getRange(i.range.rowStart,1,1,3).getValues();
  let n = rData[0][0];
  let e = rData[0][1];
  
  let msg = "Hej" "+ n +" 
  
  "Mange tak for snakken." 
   <br>
  "Som aftalt kommer her et indmeldelseslink. Når du trykker på linket, skal du egentlig bare følge instruktionerne. Hvis du har nogen spørgsmål, er du altid velkommen til at skrive."
 <br>
  "Når du har oprettet dig, så vil jeg anbefale dig at snuse lidt rundt på forummet og gå ind i din elevkonto og se velkomstfilmene." 
 <br>
  "Vi glæder os til at komme i gang."
 <br>
  "Bedste hilsner fra mig og hele teamet på guitarakademiet"
 <br>
  Logger.log(msg);
  GmailApp.sendEmail("e", "Indmeldelseslink", msg)
}