István Erdő

Erdő István

a Bridge Budapest ösztöndíjasa, Samebug, Budapest, 2017

Automatikus felhasználói levelek a Google Apps Script segítségével (3. rész)

A szkriptek egyesítése és időbélyegző hozzáadása az adatbázishoz

Az <a href=”Link to Part 1”>1.</a> és a <a href=”Link to Part 2”>2. részben</a> létrehoztunk egy alkalmazást, amely Google-táblázatokból nyeri az adatokat, CSV-formátumúvá konvertálja őket, a CSV-fájlból létrehoz egy mátrixot, majd létrehoz egy Gmail-piszkozatot személyre szabott üzenetekkel és sablonokkal. Ez a rész az utolsó simításokról szól: Kezdetként egyesítjük a „Convert to CSV” (konvertálás CSV-formátumra) és a „create personalised daft” (személyre szabott piszkozat létrehozása) elnevezésű szkripteket, hogy ne kelljen manuálisan másolgatunk a CSV-fájl azonosítóját az alkalmazás minden egyes futtatásakor. Ezután kódot írunk, amely megkeresi a felhasználóazonosítót az adatbázisban és az aktuális dátumot beírja ugyanannak a sornak a megfelelő oszlopába, jelezve, hogy azon a napon kiküldtük a levelet. Lássunk is hozzá!

A teljes kód hozzáférhető az én Github-fiókomban:

https://github.com/istvanerdo/google-script-autodraft/blob/master/code

Szkriptek egyesítése

Nyissuk meg a korábban megírt 2 szkriptfájlt és hozzunk létre egy újat. Az új fájlba másoljuk át a táblázatot CSV-fájllá konvertáló kódot, majd közvetlenül alá a személyre szabott piszkozatot létrehozó kódot.

Az alkalmazás eddig új fájlt generált, ha CSV-formátumba konvertáltattunk vele. Most már elhagyhatjuk ezt a lépést, hiszen nem kell tárolnunk a fájltartalmakat, ha később kezdeni szeretnénk velük valamit.

Tekintsük a CSV-fájlt létrehozó részt:

  // create a file in the Docs List with the given name and the csv data

  DriveApp.createFile(fileName, csvFile);

és cseréljük le erre:

  // create draft from csv

  makeDraft(csvFile)

Az utóbbi kód hajtja végre a makeDraft-függvényt a csvFile-változón, amelyet korábban már definiáltunk.

A csvFile-változó már tartalmazza az adatokat, úgyhogy a makeDraft-függvényből töröljük ki az adatokat beemelő részt:

  // Gets file content

  var data = DriveApp.getFileById(„…”).getBlob().getDataAsString();

Az elsődleges függvényt is átneveztem, mert most már a végleges piszkozatunkat hozza létre. Így tehát a saveAsCSV elnevezés helyett finalDraft-nak hívhatjuk.

Most pedig teszteljünk, és lássuk, úgy működik-e, ahogyan szeretnénk. Ügyeljünk arra, hogy a futtatás és hibakeresés ikonok melletti legördülő menüben kiválasszuk a végrehajtandó függvényt (finalDraft).

Nagyszerű, működik! Készen vagyunk a szkriptek egyesítésével. Ha most megnyitjuk a meghajtót, láthatjuk, hogy nem hozott létre új fájlokat. Mi pedig pontosan ezt szerettük volna.

Időbélyegző hozzáadása

Már csak egy lépés van hátra: szeretnénk nyomon követni, kinek is küldtünk levelet. Ezért írunk egy kódot, amely hozzárendeli az adott dátumot a felhasználókhoz.

A kód ezen részének alapját egy Stack Overflow-val (kötegtúlcsordulással) kapcsolatos kérdés adja, amelyet user1783229 tett fel, és Serge insas válaszolt meg.

A makeDraft-függvény záró } kapcsos zárójele alatt létrehozunk egy új, addCustomValue() elnevezésű függvényt. Ennek a függvénynek 3 argumentuma lesz: sheetNamestr, searchKeystr és writeValstr, amelyek rendre annak a táblázatnak a nevét jelölik, amelyben az alkalmazással feladatot hajtatunk végre, azt a sztringet jelölik, amelyet adott oszlopban keresni fogunk, valamint azt a sztringet jelölik, amelyet beírunk a sztringkeresésben (search string in) talált sorba.

function addCustomValue(sheetNamestr, searchKeystr, writeValstr) {

}

A kód többi részét írjuk be a { } kapcsos zárójelek közé. A függvénnyel először is elő kell hívnunk azokat az adatokat, amelyeket már a kód elején létrehoztunk. Szerencsére eltároltuk a változó ss-ben. Győződjünk meg arról, hogy a megfelelő táblázatban dolgozunk-e:

  // Get all data

  var sheet = ss.getSheetByName(sheetNamestr)

  var ssdata = sheet.getDataRange().getValues()

Ahhoz, hogy ez működhessen, az ss-t hozzá kell adni annak a függvénynek az argumentumaihoz, amelyben használni szeretnénk. Ha ezt a lépést kihagyjuk, akkor az alkalmazás nem fogja észlelni, hogy az általunk az addCustomValue-függvényben használt ss megegyezik a finalDraft-függvényben használt ss-szel. Menjünk vissza oda, ahol az addCustomValue-t definiáltuk, és írjuk oda az ss-t az argumentumlista végére. Csináljuk meg a makeDraft-tal is. Menjünk vissza oda, ahol a makeDraft-ot hívtuk, és adjuk hozzá ehhez is az ss-t.

Az ssdata változó most már a táblázatban található összes adatot jelenti. A for-hurokkal a táblázat minden egyes során végigmehetünk, és beállíthatjuk a meghatározott értékeket. A for-hurok felépítése a következő: for (1. állítás; 2. állítás; 3. állítás) {végrehajtandó kódrészlet}. Az 1. állítás minden egyes hurok (kódrészlet) előtt végrehajtódik, a 2. állítás a hurok futtatásának feltétele, a 3. állítás pedig az egyes hurkok után hajtódik végre. Erre tekintettel hozzuk létre a for-hurkunkat (n a sor száma, ahol 0 az első sor):

  // Loop through the data

  for (n=0; n<ssdata.length; ++n) {

  }

Vagyis minden egyes hurok esetében ellenőrizzük, hogy az aktuális hurokszám kisebb-e mint az adat hossza (mivel csak az információtartalommal rendelkező sorokon szeretnénk végigmenni, nem pedig sokezer üres cellából álló soron), ha a feltétel teljesül, akkor végrehajtja a { } kapcsos zárójelek közé írt kódot, ha nem, akkor leáll.

Most pedig a { } kapcsos zárójeleken belüli kódrészlethez hozzáadunk egy ha-feltételt, hogy ellenőrizzük a megfelelőséget:

    // Rewrite value

    if (ssdata[n][1-1].toString()==searchKeystr){ 

      ssdata[n][7-1] = writeValstr

    }

Ez az aktuális sor 7. oszlopát az általunk meghatározott writeValstr értékével teszi egyenlővé, amikor a függvényt hívja, amennyiben a ( ) zárójelben megadott feltételünk fennáll. A feltétel az, hogy az alkalmazásnak az aktuális sor [n] első oszlopának [1-1] értékét sztringgé konvertálja, és ellenőrzi, hogy az általunk meghatározott searchKeystr megegyezik-e ezzel az értékkel.

Ügyelnünk kell arra, hogy ez nem állítja át az értéket a writeValstr-re, csak eltárolja azt. A változások mentéséhez az egész táblázaton alkalmazni kell a setValue-t. Vegyük a táblázatváltozónkat; vegyük a tartományt a getRange() lehetőséggel, amelynek az argumentumai: kezdősor, kezdőoszlop, a sorok száma és az oszlopok száma (az ezzel kapcsolatos további tudnivalókhoz olvassunk bele a dokumentációba); és állítsuk be az ssdata értékeit. Tegyük a függvényen kívülre, mivel ezt csak egyszer kell elvégeznünk az addCustomValue-függvényben.

  sheet.getRange(1,1,ssdata.length,ssdata[0].length).setValues(ssdata)

Az ssdata.length értéket tartalmazó rész a sorok számát veszi, az ssdata[0].length pedig az első sor oszlopainak számát. Ezt a függvényt minden egyes felhasználóra elő kell hívnunk, úgyhogy menjünk vissza oda, ahol a createDraft-függvényt hívtuk és írjuk be alá a következő kódot:

    // Add date stamp to registered_users sheet

    addCustomValue(‘Sheet1’, userId, Utilities.formatDate(new Date(), „GMT+1”, „yyyy/MM/dd”), ss)

Értéket szeretnénk hozzáadni a Sheet1 (vagy tetszőleges) elnevezésű laphoz, meg szeretnénk keresni a felhasználóazonosítót (userId) az első oszlopban és a jelenlegi dátumot be szeretnénk írni a 6. oszlopba.

Az alkalmazás még teljesebbé tételéhez adjunk hozzá még néhány kódsort. Az a rész, ahol azt az utasítást adtuk az alkalmazásnak, hogy térjen vissza, ha nincsen e-mail, sokkal teljesebbé tehető az új függvénnyel, úgyhogy cseréljük le a következőre:

    // Put X if no email

    if(email === „NULL”){ 

      addCustomValue(‘Sheet1’, userId, „X”, ss)

      return

    }

Ez egy nagy X-betűt ír az Elküldve fejlécű oszlopba, és csak utána megy tovább, ahelyett, hogy egyből továbblépne. Ugyanide írjunk le még egy esetet, amikor át szeretnénk ugrani a sort, ha az adott személynek már küldtünk levelet (vagyis amikor a letterSent oszlop nem üres, !=””):

    //Return if already written email to the user

    if(letterSent != „”) return

Még nem specifikáltuk a letterSent-et, szóval menjünk vissza az oszlopváltozók listájához, és adjuk hozzá a következőket:

    var letterSent = cell[6]

A legutolsó feladatunk, hogy lefuttassuk és imádkozzunk, hogy működjön. A teljes kód az alábbi:

function finalDraft() {

  var ss = SpreadsheetApp.openById(„1zqAfLfaoR7DMdRu1HGi4isWLoip2oZXwrX308IK0Jgw”);

  Logger.log(ss.getName());

  var sheets = ss.getSheets();

  var sheet = sheets[0];

  // append „.csv” extension to the sheet name

  fileName = sheet.getName() + „.csv”;

  // convert all available sheet data to csv format

  var csvFile = convertRangeToCsvFile_(fileName, sheet);

  // create draft from csv

  makeDraft(csvFile, ss)

}

function convertRangeToCsvFile_(csvFileName, sheet) {

  // get available data range in the spreadsheet

  var activeRange = sheet.getDataRange();

  var data = activeRange.getValues();

  var csvFile = undefined;

  // loop through the data in the range and build a string with the csv data

  if (data.length > 1) {

    var csv = „”;

    for (var row = 0; row < data.length; row++) {

      for (var col = 0; col < data[row].length; col++) {

        if (data[row][col].toString().indexOf(„,”) != -1) {

          data[row][col] = „\”” + data[row][col] + „\””;

        }

      }

      // join each row’s columns

      // add a carriage return to end of each row, except for the last one

      if (row < data.length-1) {

        csv += data[row].join(„,”) + „\r\n”;

      }

      else {

        csv += data[row];

      }

    }

    csvFile = csv;

  }

  return csvFile;

}

function makeDraft(data, ss) {

  // Split into lines

  var allRows = data.split(„\n”)

  var subject = „Please provide feedback”

  var messages = [

        „1Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,

        „2Hi $name,\n\nLorem ipsum dolor sit amet, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,

        „3Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit LINK. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”,

        „4Hi $name,\n\nLorem ipsum dolor sit amet LINK, consectetur adipiscing elit. Fusce sed.\n\nThank you,\nIstvan\n– \nIstvan Erdo\nCustomer Relations @samebug\nhttps://samebug.io”

  ]

  // Loop through all rows

  allRows.map(function(line, key) {

    if(key === 0) return

    var cell = line.trim().split(„,”)

    var userId = cell[0]

    var displayName = cell[1]    

    var name = cell[2]

    var email = cell[3]

    var templateNumber = cell[4]

    var search = cell[5]

    var letterSent = cell[6]

    var hasName = name && name !== „”

    // Use first name as displayname

    if(hasName) {

      var fullName = name.split(” „)

      displayName = fullName ? fullName[0] : displayName

    }

    // Put X if no email

    if(email === „NULL”){ 

      addCustomValue(‘Sheet1’, userId, „X”, ss)

      return

    }

    //Return if already written email to the user

    if(letterSent != „”) return

    //Return if no user ID

    if(userId === „”) return

    //Set and personalise messages

    var template = messages[templateNumber * 1 – 1]

    .replace(„$name”, displayName)

    .replace(/LINK/g, search)

    GmailApp.createDraft(email, subject, template)

    // Add date stamp to registered_users sheet

    addCustomValue(‘Sheet1’, userId, Utilities.formatDate(new Date(), „GMT+1”, „yyyy/MM/dd”), ss)

  })

}

function addCustomValue(sheetNamestr, searchKeystr, writeValstr, ss) {

  // Get all data

  var sheet = ss.getSheetByName(sheetNamestr)

  var ssdata = sheet.getDataRange().getValues()

  // Loop through the data

  for (n=0; n<ssdata.length; ++n) {

    // Rewrite value

    if (ssdata[n][1-1].toString()==searchKeystr){ 

      ssdata[n][7-1] = writeValstr

    }

  }

  sheet.getRange(1,1,ssdata.length,ssdata[0].length).setValues(ssdata)

}

A finalDraft futtatása után:

Megvannak a piszkozataink.

És ki is van töltve a Levél elküldve fejlécű oszlop. Ha ismét lefuttatjuk a kódot, akkor láthatjuk, hogy nem hoz létre több piszkozatot, mivel már minden egyes sorra ki van tölte a Levél elküldve fejlécű oszlop.

Készen is vagyunk az alkalmazásunkkal. Köszönöm, hogy elolvastad ezt az útmutatót, remélem, mutathattam valami újat. Kérdés esetén állok rendelkezésre a [email protected] e-mailcímen vagy a Facebook-on, ezen az elérhetőségen: www.facebook.com/samebug.io. Kövesd blogunkat: blog.samebug.io.