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.
