code.gs

// Application constants
const APP_TITLE = 'Generate and send PDFs';
const OUTPUT_FOLDER_NAME = "Factures_CA2627_send";
const INVOICE_TEMPLATE_SHEET_NAME = 'Invoice';
const str_error = "Error mail" ;

const nomber_of_email_type = 6 ;

var ok_emails =["Pending Approval", "Confirmed", "Paiement Received", "Final Info", "Reminder"] ;
var date_emails=["Date Pending Approval", "Date Confirmed", "Date Paiement", "Date Final Info", "Date Reminder"] ;
var subject_emails = ["Pending Approval Subject", "Confirmed Subject", "Paiement received subject", "Final info subject", "Reminder subject"] ;
var msg_emails = ["Pending Approval Msg", "Confirmed Msg", "Paiement received Msg", "Final Info Msg", "Reminder Paiement Msg"] ;

ok_emails.push("Invoice")
date_emails.push("Date Invoice")
subject_emails.push("Invoice Subject")
msg_emails.push("Invoice Msg")

var ss = SpreadsheetApp.getActiveSpreadsheet()
var tech_mails = ss.getSheetByName("Tech_mails") ;
var interrup_indice =  getCellValueByColumnName(tech_mails, "Indice Interruption", 2)
var form_name =  getCellValueByColumnName(tech_mails, "Name Form", 2)
var attendee_mail_column = getCellValueByColumnName(tech_mails, "Col email name", 2)
var attendee_name_column = getCellValueByColumnName(tech_mails, "Col nom name", 2)
var attendee_surname_column = getCellValueByColumnName(tech_mails, "Col prenom name", 2)
var attendee_adress_column = getCellValueByColumnName(tech_mails, "Col adress name", 2)
var attendee_code_column = getCellValueByColumnName(tech_mails, "Col code name", 2)
var attendee_price_column = getCellValueByColumnName(tech_mails, "Col price name", 2)

//const str_dm_fact = "Date Invoice" ;
//const str_dm_part = "Date mail attente part" ;

const invoiceTemplateSheet = ss.getSheetByName("Invoice");
const ssId = ss.getId()

var now = new Date();

const IS_OPERATIONAL = true // false = debug mode, envoie les mails à raphael et n'écrit aucune date

function OK_recep(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var rep_form = ss.getSheetByName(form_name) ;
  var last_row = rep_form.getLastRow() ;
  var ok_email= ok_emails[0] ;
  var date_email = date_emails[0] ;
  var subject_email = subject_emails[0] ;
  var msg_email = msg_emails[0] ;

  for (var i=last_row; i > 2; i--){
    if (getCellValueByColumnName(rep_form, ok_email, i) == "OK"){
      break;
    }

    setCellValueByColumnName(rep_form, ok_email, i, "OK") 

    message = getCellValueByColumnName(rep_form, msg_email, i)
    topic = getCellValueByColumnName(rep_form, subject_email, i)

    if (IS_OPERATIONAL) {
      mail = getCellValueByColumnName(rep_form, attendee_mail_column, i)
    } else {
      mail = "raphael.of.p@gmail.com" ;
    }

    try {
      GmailApp.sendEmail(mail,topic,message);
    
      if (IS_OPERATIONAL) {
        setCellValueByColumnName(rep_form, date_email, i, now) // If mail is send, write the date 
      }

    } catch(e) {
      Logger.log("Error with email (" + mail + "). " + e);
      setCellValueByColumnName(rep_form, str_error, i, "Error with email")
    }
  }
}

function emails() {

// Récupère les données

var userEmail = Session.getActiveUser().getEmail();

//if (userEmail =="westievillageinfo@gmail.com" || userEmail =="raphael.of.p@gmail.com"){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var rep_form = ss.getSheetByName(form_name) ;
  //var tech_mails = ss.getSheetByName("Tech_mails") ;
  //var template_mail = ss.getSheetByName("Template mails") ;
  var last_row = rep_form.getLastRow() ;
  var is_fact_mail = false

  var mail = ""

  var interrup_indice =  getCellValueByColumnName(tech_mails, "Indice Interruption", 2)
  var start_indice =  getCellValueByColumnName(tech_mails, "Indice start", 2)

  if(start_indice < interrup_indice){
    start_indice = interrup_indice ;
  }

  for (var i=start_indice; i < last_row+1; i++){

    setCellValueByColumnName(tech_mails, "Indice Interruption", 2, i)

    var message = "";
    var attachments = [{}] ;

  
    for (var j=0 ; j < nomber_of_email_type; j++){
      var ok_email= ok_emails[j] ;
      var date_email = date_emails[j] ;
      var subject_email = subject_emails[j] ;
      var msg_email = msg_emails[j] ;

      is_OK = getCellValueByColumnName(rep_form, ok_email, i)
      is_date = getCellValueByColumnName(rep_form, date_email, i)

      if (is_OK=="OK" && is_date==""){
        message = getCellValueByColumnName(rep_form, msg_email, i)
        topic = getCellValueByColumnName(rep_form, subject_email, i)

        if (subject_email == "Invoice Subject"){
          customer_name = getCellValueByColumnName(rep_form, attendee_name_column, i) + " " + getCellValueByColumnName(rep_form, attendee_surname_column, i) ;
          customer_adress = getCellValueByColumnName(rep_form, attendee_adress_column, i)
          invoiceNumber = getCellValueByColumnName(rep_form, attendee_code_column, i);
          price = getCellValueByColumnName(rep_form, attendee_price_column, i)
          var pdfID = processDocuments(customer_name, customer_adress, price, invoiceNumber, invoiceTemplateSheet, ss)
          var attachment = DriveApp.getFileById(pdfID);
        }

        if (IS_OPERATIONAL) {
          mail = getCellValueByColumnName(rep_form, attendee_mail_column, i)
        } else {
          mail = "raphael.of.p@gmail.com" ;
        }

        try {
          if (subject_email == "Invoice Subject"){
            GmailApp.sendEmail(mail, topic, message, {
              attachments: [attachment.getAs(MimeType.PDF)],
              name: 'West In Lille',
            });
          }
          else{
            GmailApp.sendEmail(mail,topic,message);
          }

          if (IS_OPERATIONAL) {
            setCellValueByColumnName(rep_form, date_email, i, now) // If mail is send, write the date 
          }

        } catch(e) {
          Logger.log("Error with email (" + mail + "). " + e);
          setCellValueByColumnName(rep_form, str_error, i, "Error with email")
        }

      }
    }
    }
    setCellValueByColumnName(tech_mails, "Indice Interruption", 2, 2)
  //}
    
   // if (is_facture == "OK"){
   //   if (is_mail_fact == ""){
   //     message =  getCellValueByColumnName(rep_form, "Mail facture", i)
   //     pass = getCellValueByColumnName(rep_form, "Type d'inscription ", i)
   //     prix = getCellValueByColumnName(rep_form, "Prix", i)
   //     customer_name = getCellValueByColumnName(rep_form, "NOM", i) + " " + getCellValueByColumnName(rep_form, "Prénom", i) ;
   //     customer_adress = getCellValueByColumnName(rep_form, "Tu veux une facture ?", i)
   //     invoiceNumber = "CA252600" + getCellValueByColumnName(rep_form, "Code", i);
   //     pdfblob = processDocuments(customer_name, customer_adress, prix, pass, invoiceNumber, ss)


        // setCellValueByColumnName(rep_form, str_dm_fact, i, now)
   //     str_case_date = str_dm_fact

    //    is_fact_mail = true ;
    //    is_send_new_mail = true ; 
    //  }
    //}


  //    else if (parseInt((now-is_mail_confirm)/(24*3600*1000)) > nbre_jour_relance){ // Tous les XXj
  //      if (is_relance != "NON"){
  //        message = getCellValueByColumnName(rep_form, "Mail relance paiement", i)
          //setCellValueByColumnName(rep_form, str_dm_confirm, i, now) ;
  //        str_case_date = str_dm_confirm
  //        is_send_new_mail = true ; 
  //      }
  //    }
    
  
}

function getCellValueByColumnName(sheet, columnName, row) {
  let cell = getCellRangeByColumnName(sheet, columnName, row);
  if (cell != null) {
    return cell.getValue();
  }
}

function getCellRangeByColumnName(sheet, columnName, row) {
  let data = sheet.getDataRange().getValues();
  let column = data[0].indexOf(columnName);
  if (column != -1) {
    return sheet.getRange(row, column + 1, 1, 1);
  }
}

function getCellValueByLineName(sheet, lineName, col) {
  let cell = getCellRangeByLineName(sheet, lineName, col);
  if (cell != null) {
    return cell.getValue();
  }
}

function getCellRangeByLineName(sheet, lineName, col) {
  let data = sheet.getDataRange().getValues();
  transposedata = Transpose(data);
  let row = transposedata[0].indexOf(lineName);
  if (row != -1) {
    return sheet.getRange(row + 1, col, 1, 1);
  }
}

function Transpose(a){
  return Object.keys(a[0]).map( function (c) { return a.map(function (r) { return r[c];}); });
}

function setCellValueByColumnName(sheet, columnName, row, new_value) {
  let cell = getCellRangeByColumnName(sheet, columnName, row);
  return cell.setValue(new_value);
}


/**
 * Populating the template sheet with  customer data, then saves as a PDF document.
 * 
 * Called by user via custom menu item.
 */
function processDocuments(customer_name, customer_adress, price, invoiceNumber, templateSheet, ss) {

  //ss.toast("Creating Invoices", APP_TITLE, 1);
  //const invoices = [];

  //ss.toast(`Creating Invoice for ${customer.customer_name}`, APP_TITLE, 1);
  const pdfID = createInvoiceForCustomer(
    customer_name,
    customer_adress,
    price,
    invoiceNumber,
    templateSheet,
    ss.getId(),
  );
  //invoices.push(invoice);
  
  // Writes invoices data to the sheet.
  return pdfID
}


function createInvoiceForCustomer(customer_name, customer_adress, price, invoiceNumber, templateSheet, ssId) {

  // Clears existing data from the template.
  //clearTemplateSheet();

  // Calulates dates.
  const todaysDate = new Date().toDateString()

  // Sets values in the template.
  templateSheet.getRange('B9').setValue(customer_name)
  templateSheet.getRange('B10:B13').setValue(customer_adress)
  templateSheet.getRange('F9').setValue(invoiceNumber)
  templateSheet.getRange('F12').setValue(todaysDate)
  templateSheet.getRange('F19:F20').setValue(price)

  // Cleans up and creates PDF.
  SpreadsheetApp.flush();
  Utilities.sleep(500); // Using to offset any potential latency in creating .pdf
  const pdf = createPDF(
    ssId,
    templateSheet,
    `Invoice#${invoiceNumber}-${customer_name}`,
  );
  return pdf.getId()
}

/**
* Resets the template sheet by clearing out customer data.
* You use this to prepare for the next iteration or to view blank
* the template for design.
* 
* Called by createInvoiceForCustomer() or by the user via custom menu item.
*/
function clearTemplateSheet() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const templateSheet = ss.getSheetByName(INVOICE_TEMPLATE_SHEET_NAME);
  // Clears existing data from the template.
  const rngClear = templateSheet.getRangeList(['B9', 'B10:B13', 'F9', 'F12', 'B19:B20', 'F19:F20']).getRanges()
  rngClear.forEach(function (cell) {
    cell.clearContent();
  });

}

/**
 * Creates a PDF for the customer given sheet.
 * @param {string} ssId - Id of the Google Spreadsheet
 * @param {object} sheet - Sheet to be converted as PDF
 * @param {string} pdfName - File name of the PDF being created
 * @return {file object} PDF file as a blob
 */
function createPDF(ssId, sheet, pdfName) {
  const fr = 0;
  const fc = 0;
  const lc = 9;
  const lr = 27;
  const url = `https://docs.google.com/spreadsheets/d/${ssId}/export?format=pdf&size=7&fzr=true&portrait=true&fitw=true&gridlines=false&printtitle=false&top_margin=0.5&bottom_margin=0.25&left_margin=0.5&right_margin=0.5&sheetnames=false&pagenum=UNDEFINED&attachment=true&gid=${sheet.getSheetId()}&r1=${fr}&c1=${fc}&r2=${lr}&c2=${lc}`;

  const params = {
    method: "GET",
    headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
  };
  const blob = UrlFetchApp.fetch(url, params)
    .getBlob()
    .setName(`${pdfName}.pdf`);

  // Gets the folder in Drive where the PDFs are stored.
  const folder = getFolderByName_(OUTPUT_FOLDER_NAME);

  const pdfFile = folder.createFile(blob);
  return pdfFile;
}