Question

0

499

Views

How can I get all data in a unique message, from a sheet A from comparing two cells in different spreadsheet

I want to compare two cells in sheetA and SheetB like in the screens below to get the exact results (screen).

I've run a code, but it sends me multiple messages (Each single data row in a message) instead of a single message containing all the information (See results screen).

SheetA SheetB Results Thank you in advance.

Here's the code I used :

function myFunction(){

  var SheetA = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SheetA');
  var SheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SheetB');
  var lr = SheetA.getLastRow();

  for (i = 2; i < lr+1; i++){

    var StoreB = SheetB.getRange(i,1).getValue();
    var StoreA = SheetA.getRange(2,2).getValue();
    var Activity = SheetA.getRange(2,4).getValue(); 

    if (StoreA == StoreB && Activity == 'Actif'){

      var order = SheetB.getRange(i,2).getValue();
      var date_li = SheetB.getRange(i,4).getValue();
      var date = Utilities.formatDate(new Date(date_li), 'Europe/Paris', 'dd/MM/yyyy');
      var ref = SheetB.getRange(i,5).getValue();
      var desi = SheetB.getRange(i,6).getValue();
      var quantity = SheetB.getRange(i,7).getValue();
      var livred = SheetB.getRange(i,8).getValue();
      var email = SheetA.getRange(2,5).getValue();
        
      
      const htmlTemplate = HtmlService.createTemplateFromFile('Body');
      
      htmlTemplate.email = email;
      htmlTemplate.order = order;
      htmlTemplate.date = date;
      htmlTemplate.desi = desi;
      htmlTemplate.ref = ref;
      htmlTemplate.quantity = quantity;
      htmlTemplate.livred = livred;

      const htmlforEmail = htmlTemplate.evaluate().getContent();

      console.log(htmlforEmail)
      MailApp.sendEmail(
        email,
        'Modification date d\'inventaire',
        "SVP Ouvrez ce mail avec le support HTML",
        {htmlBody: htmlforEmail}
      );
    }
  }
}

about 4 years ago · Juan Pablo Isaza

2 answers

Answer question

0

Answer question

Find remote jobs

Answer 1 · 2022-07-09T03:31:43.021Z

This is how I would do it but it will necessitate having to change the template so that it can handle a 2d array to place the data in to a table. Fortunately, the data also includes the first row of headers for the table.

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sha = ss.getSheetByName('SheetA');//reference
  const shb = ss.getSheetByName('SheetB');//order data
  const hA = shb.getRange(1,1,1,8).getDisplayValues().flat().splice(2,1);
  const vsb = shb.getRange(2, 1, shb.getLastRow() - 1, shb.getLastColumn()).getDisplayValues();
  const StoreA = sha.getRange(2, 2).getValue();
  const Activity = sha.getRange(2, 4).getValue();
  const email = sha.getRange(2, 5).getValue();
  const del = { pA: [] };
  if (Activity == 'Actif') {
    vsb.forEach((r, i) => {
      let [st, order, , date_li, date, desi, quantity, livred] = r
      if (StoreA == st) {
        if (!del.hasOwnProperty(st)) {
          del[st] = [];
          del[st] = hA;//puts the titles into the data
          del[st].push([r[0], r[1], r[3], r[4], r[5], r[6], r[7]]);
          del.pA.push(st);
        } else {
          del[st].push([r[0], r[1], r[3], r[4], r[5], r[6], r[7]]);
        }
      }
    });
    const htmlTemplate = HtmlService.createTemplateFromFile('Body');
    htmlTemplate.order = del[st];
  }
  MailApp.sendEmail(email, 'Modification date d\'inventaire', "SVP Ouvrez ce mail avec le support HTML", { htmlBody: htmlTemplate.evaluate().getContent() });
}

Answer 2 · 2022-07-09T03:31:43.029Z

Try this

function myFunction(){

  var SheetA = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SheetA');
  var SheetB = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SheetB');
  var lr = SheetA.getLastRow();

      var order = "";
      var date_li = ""
      var date = "";
      var ref = "";
      var desi = "";
      var quantity = "";
      var livred = "";
      var email = "";
        


  for (i = 2; i < lr+1; i++){

    var StoreB = SheetB.getRange(i,1).getValue();
    var StoreA = SheetA.getRange(2,2).getValue();
    var Activity = SheetA.getRange(2,4).getValue(); 

    if (StoreA == StoreB && Activity == 'Actif'){

      order += SheetB.getRange(i,2).getValue()+'\n';
      date_li += SheetB.getRange(i,4).getValue()+'n';
      date += Utilities.formatDate(new Date(date_li), 'Europe/Paris', 'dd/MM/yyyy')+'\n';
      ref += SheetB.getRange(i,5).getValue()+'\n';
      desi += SheetB.getRange(i,6).getValue()+'\n';
      quantity += (+SheetB.getRange(i,7).getValue());
      livred = SheetB.getRange(i,8).getValue();
      email = SheetA.getRange(2,5).getValue();
      
    }

      
      const htmlTemplate = HtmlService.createTemplateFromFile('Body');
      
      htmlTemplate.email = email;
      htmlTemplate.order = order;
      htmlTemplate.date = date;
      htmlTemplate.desi = desi;
      htmlTemplate.ref = ref;
      htmlTemplate.quantity = quantity;
      htmlTemplate.livred = livred;
      const htmlforEmail = htmlTemplate.evaluate().getContent();


      console.log(htmlforEmail)
      MailApp.sendEmail(
        email,
        'Modification date d\'inventaire',
        "SVP Ouvrez ce mail avec le support HTML",
        {htmlBody: htmlforEmail}
      );
  }
}

````

0

499

How can I get all data in a unique message, from a sheet A from comparing two cells in different spreadsheet

2 answers

0

0

Find remote jobs

Andres GPT