docs
See also Slides
Custom Function met URL Fetch
function MY_MATCH(lastname, bdate_iso) {
let url = "https://URL?lastname="+lastname+"%2Fq%2Fperson_search_birthdate%2Fp%2Fvalue%2F"+bdate_iso+"%2Fresults%2Ccount%3Fcount%3D24%26offset%3D0"
var res = UrlFetchApp.fetch(url);
json = JSON.parse(res.getContentText())
return json["data"][0].items.length!=0;
}
lijst met oplopende datums
type een datum in en sleep het blauwe blokje van de cel naar beneden.
download shared spreadsheet as csv
- [ ] CSV automatisch als Export wanneer ge-shared: https://docs.google.com/spreadsheets/d/1gJRrqqBQP17iH_Vs5t3dE4nZSUcavhkJMhVxF12L19E/export?format=csv
- [ ] CSV via Publish to Web: https://docs.google.com/spreadsheets/d/e/2PACX-1vTV0ddS6dyYl4LzvLGSfRru32Ty158yCp_8lsvzhFov-MAvyBIO5-o1KWLs44a0oZ83D3JgQKCO8LAN/pub?gid=0&single=true&output=csv
- [ ] JSON via Publish to Web: https://spreadsheets.google.com/feeds/list/1gJRrqqBQP17iH_Vs5t3dE4nZSUcavhkJMhVxF12L19E/od6/public/values?alt=json
regex
see also regex
lookup
=LOOKUP(B2;Trefwoordsoorten!A:A;Trefwoordsoorten!C:C)
ArrayFormula voor elke regel van een sheet
Automatische links in spreadsheet kolom voor elke regel 'place this in the header cell above a column, that way you will still be able to sort the sheet
=ArrayFormula(if(A1:A<>"",hyperlink("https://....."&A1:A,"title:"&A1:A),))
join, unique, filter, countunique
=JOIN(", " ; (UNIQUE(FILTER(LOG!C:C;LOG!D:D=A2))))
=COUNTUNIQUE(filter(log!C:C;log!D:D=A2))
polling spreadsheet with script
add dates
add 12 months to date
=EDATE(A11;12)
unique / sort / proper
you can use this as a nice data validator
=SORT(PROPER(UNIQUE(INKOMSTEN!D3:D)))
UrlFetchApp.fetch
var text = UrlFetchApp.fetch(URL).getContentText();
header("Content-type: text/plain");
define('DB_NAME', '...');
define('DB_USER', '...');
define('DB_PASSWORD', '...');
define('DB_HOST', '...');
$db = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db(DB_NAME, $db);
$id = mysql_real_escape_string($_GET["id"]);
if (!is_numeric($id)) die();
if ($row = mysql_fetch_assoc(mysql_query("SELECT post_id,meta_value FROM wp_postmeta WHERE meta_key='_billing_country' AND post_id=$id"))) {
echo $row['meta_value'];
}
if ($row = mysql_fetch_assoc(mysql_query("SELECT post_id,meta_value FROM wp_postmeta WHERE meta_key='VAT Number' AND post_id=$id"))) {
echo " ".$row['meta_value'];
}
tips
http://woorkup.com/2010/02/19/10-useful-google-spreadsheet-formulas-you-must-know/
unique() and continue() functions
=UNIQUE(A:A)
=CONTINUE(B1; 2; 1)
...etc..
append row
sheet.appendRow(['hello',true,5.5,new Date()]);
fetch from url
var text = UrlFetchApp.fetch("http://companje.nl").getContentText();
documentation
spreadsheet as csv
scripting with csv
advanced scripting
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var entries = [ {name: "Layout voor kolommen instellen", functionName: "layoutKolommen"},
{name: "BTW berekening toevoegen", functionName: "btwBerekening"},
{name: "Kostensoort validator toevoegen", functionName: "soortValidator"}
];
ss.addMenu("Administratie", entries);
}
function soortValidator() {
var range = SpreadsheetApp.getActiveRange();
if (range.getColumnIndex()!=10 || range.getNumColumns()!=1) {
Browser.msgBox("Selecteer de kolom met kostensoorten aub.");
return;
}
var overzicht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Overzicht");
var from = overzicht.getRange("Overzicht!A10"); //soort validator
var to = SpreadsheetApp.getActiveRange();
from.copyTo(to);
}
function btwBerekening() {
var range = SpreadsheetApp.getActiveRange();
if (range.getColumnIndex()# 6 && range.getNumColumns()# 1) range.setFormula("=(R[0]C[3]/(R[0]C[1]*100+100))*100"
else if (range.getColumnIndex()# 8 && range.getNumColumns()# 1) range.setFormula("=R[0]C[-1]*R[0]C[-2]"
else Browser.msgBox("Selecteer één van de volgende kolommen: excl. BTW óf BTW bedrag.");
}
function layoutKolommen() {
if (SpreadsheetApp.getActiveSheet().getName()# "Overzicht")
Browser.msgBox("'Layout verbeteren' is bedoeld voor Inkomsten en Uitgaven bladen");
return;
}
var sheet = SpreadsheetApp.getActiveSheet();
sheet.setFrozenRows(1);
sheet.setColumnWidth(1,80); //datum
sheet.setColumnWidth(2,50); //kwartaal
sheet.setColumnWidth(3,80); //factuur nr
sheet.setColumnWidth(4,250); //debiteur/crediteur
sheet.setColumnWidth(5,270); //omschrijving
sheet.setColumnWidth(6,70); //excl
sheet.setColumnWidth(7,70); //%
sheet.setColumnWidth(8,70); //btw bedrag
sheet.setColumnWidth(9,70); //inc
sheet.setColumnWidth(10,120); //soort
sheet.setColumnWidth(11,270); //soort
sheet.getRange("A:A").setNumberFormat("yyyy-MM-dd");
sheet.getRange("F:F").setNumberFormat("€ #,##0.00");
sheet.getRange("G:G").setNumberFormat("0.00%");
sheet.getRange("H:H").setNumberFormat("€ #,##0.00");
sheet.getRange("I:I").setNumberFormat("€ #,##0.00");
}
new insights
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [ {name: "Upload Rabobank bestand...", functionName: "upload"} ];
sheet.addMenu("Administratie", entries);
}
function upload() {
var app = UiApp.createApplication().setTitle("Upload Rabobank mut.txt");
var form = app.createFormPanel().setId("frm").setEncoding("multipart/form-data");
var formContent = app.createVerticalPanel();
form.add(formContent);
formContent.add(app.createLabel("Ga naar www.rabobank.nl en download mut.txt voor het juiste kwartaal."));
formContent.add(app.createLabel("Bijvoorbeeld voor K3: 01-07-2012 t/m 30-09-2012."));
formContent.add(app.createLabel("."));
formContent.add(app.createFileUpload().setName("thefile"));
formContent.add(app.createSubmitButton("Upload"));
app.add(form);
SpreadsheetApp.getActiveSpreadsheet().show(app);
}
function doPost(e) {
var fileBlob = e.parameter.thefile;
var doc = DocsList.createFile(fileBlob);
var app = UiApp.getActiveApplication();
app.add(app.createLabel("file uploaded successfully"));
importCsv(doc.getName());
return app;
}
function importCsv(filename) {
if (!filename) filename = Browser.inputBox("Welk bestand wil je importeren?");
if (!filename) return;
var files = DocsList.find(filename);
var csv = CSVToArray(files[0].getContentAsString());
var inkomsten = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inkomsten");
var uitgaven = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Uitgaven");
for (var i=0; i<csv.length; i++) {
if (csv[i].length!=16) continue;
var year = csv[i][2].substr(0,4);
var month = csv[i][2].substr(4,2);
var day = csv[i][2].substr(6,2);
var isodate= year+"-"+month+"-"+day;
var quarter = "K"+Math.ceil(month/3);
var credit=(csv[i][3]# "C"
var amount=csv[i][4].replace(".",",");
var name=csv[i][6];
var invoicenr="";
var exvat="=R[0]C[3]/(R[0]C[1]+1)";
var vatpct=0;
var vatamt="=R[0]C[-2]*R[0]C[-1]";
var category="";
var description = csv[i][10];
if (csv[i][11]) description=description.trim()+" "+csv[i][11];
if (csv[i][12]) description=description.trim()+" "+csv[i][12];
if (csv[i][13]) description=description.trim()+" "+csv[i][13];
var sheet = credit ? inkomsten : uitgaven;
var nextRow = sheet.getLastRow()+1;
sheet.appendRow([isodate,quarter,invoicenr,name,description,exvat,vatpct,vatamt,amount,category]);
}
layoutSheet(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inkomsten"));
layoutSheet(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Uitgaven"));
}
function layoutSheet(sheet) {
Logger.log(sheet.getName());
sheet.setFrozenRows(1);
sheet.setColumnWidth(1,80); //datum
sheet.setColumnWidth(2,50); //kwartaal
sheet.setColumnWidth(3,80); //factuur nr
sheet.setColumnWidth(4,250); //debiteur/crediteur
sheet.setColumnWidth(5,270); //omschrijving
sheet.setColumnWidth(6,70); //excl
sheet.setColumnWidth(7,70); //%
sheet.setColumnWidth(8,70); //btw bedrag
sheet.setColumnWidth(9,70); //inc
sheet.setColumnWidth(10,120); //soort
sheet.setColumnWidth(11,270); //soort
sheet.getRange("A:A").setNumberFormat("yyyy-MM-dd");
sheet.getRange("F:F").setNumberFormat("€ #,##0.00");
sheet.getRange("G:G").setNumberFormat("0.00%");
sheet.getRange("H:H").setNumberFormat("€ #,##0.00");
sheet.getRange("I:I").setNumberFormat("€ #,##0.00");
}
function CSVToArray(strData, strDelimiter) {
strDelimiter = (strDelimiter || ",");
var arrData = [[]];
var arrMatches = null;
var objPattern = new RegExp((
"(\" + strDelimiter + "|\r?\n|\r|^)" + // Delimiters.
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" + // Quoted fields.
"([^\"\" + strDelimiter + "\r\n]*))" // Standard fields.
),"gi");
while (arrMatches = objPattern.exec(strData)) {
var strMatchedDelimiter = arrMatches[1];
if (strMatchedDelimiter.length && (strMatchedDelimiter != strDelimiter)) arrData.push([]);
var strMatchedValue = arrMatches[2] ? arrMatches[2].replace(new RegExp( "\"\"", "g" ), "\"") : arrMatches[3];
arrData[ arrData.length - 1 ].push( strMatchedValue );
}
return arrData;
}
/*function soortValidator() {
var range = SpreadsheetApp.getActiveRange();
if (range.getColumnIndex()!=10 || range.getNumColumns()!=1) {
Browser.msgBox("Selecteer de kolom met kostensoorten aub.");
return;
}
var overzicht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Overzicht");
var from = overzicht.getRange("Overzicht!A10"); //soort validator
var to = SpreadsheetApp.getActiveRange();
from.copyTo(to);
}*/
2015 versie
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [ {name: "Upload Rabobank bestand...", functionName: "upload"} ];
sheet.addMenu("Administratie", entries);
}
function upload() {
app = UiApp.createApplication().setTitle("Upload Rabobank transactions.txt");
form = app.createFormPanel().setId("frm").setEncoding("multipart/form-data");
panel = app.createVerticalPanel();
panel.add(app.createLabel("Ga naar www.rabobank.nl en download transactions.txt (nieuwe formaat) voor het juiste kwartaal. Bijvoorbeeld voor K3: 01-07-2012 t/m 30-09-2012."));
panel.add(app.createFileUpload().setName("thefile"));
panel.add(app.createSubmitButton("Upload"));
form.add(panel);
app.add(form);
SpreadsheetApp.getActiveSpreadsheet().show(app);
}
function doPost(e) {
var fileBlob = e.parameter.thefile;
csv = CSVToArray(fileBlob.contents);
Browser.msgBox("Klik op OK om te beginnen met het verwerken van " + csv.length + " transacties. Dit kan enkele minuten duren en geeft geen tussentijdse feedback.");
addTransactions(csv);
}
function addTransactions(csv) {
var inkomsten = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inkomsten"); ///getSheets()[2]; //getSheetByName("Inkomsten");
var uitgaven = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Uitgaven"); //getSheets()[1]; //getSheetByName("Uitgaven");
for (var i=0; i<csv.length; i++) {
if (csv[i].length!=19) continue;
var year = csv[i][2].substr(0,4);
var month = csv[i][2].substr(4,2);
var day = csv[i][2].substr(6,2);
var isodate= year+"-"+month+"-"+day;
var quarter = "K"+Math.ceil(month/3);
var credit=(csv[i][3]=="C");
var amount=csv[i][4].replace(".",",");
var name=csv[i][6];
if (name==undefined) name="";
var invoicenr="";
var exvat="=R[0]C[3]/(R[0]C[1]+1)";
var vatpct=0;
var vatamt="=R[0]C[-2]*R[0]C[-1]";
var category="";
var description = csv[i][10];
if (csv[i][11]) description=description.trim()+" "+csv[i][11];
if (csv[i][12]) description=description.trim()+" "+csv[i][12];
if (csv[i][13]) description=description.trim()+" "+csv[i][13];
description = description.toLowerCase();
var sheet = credit ? inkomsten : uitgaven;
var nextRow = sheet.getLastRow()+1;
Logger.log(description);
sheet.appendRow([isodate,quarter,invoicenr,name,description,exvat,vatpct,vatamt,amount,category]);
}
layoutSheet(inkomsten);
layoutSheet(uitgaven);
Browser.msgBox("Klaar met verwerken van " + csv.length + " transacties");
}
function layoutSheet(sheet) {
Logger.log(sheet.getName());
sheet.setFrozenRows(1);
sheet.setColumnWidth(1,80); //datum
sheet.setColumnWidth(2,50); //kwartaal
sheet.setColumnWidth(3,80); //factuur nr
sheet.setColumnWidth(4,250); //debiteur/crediteur
sheet.setColumnWidth(5,270); //omschrijving
sheet.setColumnWidth(6,70); //excl
sheet.setColumnWidth(7,70); //%
sheet.setColumnWidth(8,70); //btw bedrag
sheet.setColumnWidth(9,70); //inc
sheet.setColumnWidth(10,120); //soort
sheet.setColumnWidth(11,270); //soort
sheet.getRange("A:A").setNumberFormat("yyyy-MM-dd");
sheet.getRange("F:F").setNumberFormat("€ #,##0.00");
sheet.getRange("G:G").setNumberFormat("0.00%");
sheet.getRange("H:H").setNumberFormat("€ #,##0.00");
sheet.getRange("I:I").setNumberFormat("€ #,##0.00");
}
function CSVToArray(strData, strDelimiter) {
strDelimiter = (strDelimiter || ",");
var arrData = [[]];
var arrMatches = null;
var objPattern = new RegExp((
"(\" + strDelimiter + "|\r?\n|\r|^)" + // Delimiters.
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" + // Quoted fields.
"([^\"\" + strDelimiter + "\r\n]*))" // Standard fields.
),"gi");
while (arrMatches = objPattern.exec(strData)) {
var strMatchedDelimiter = arrMatches[1];
if (strMatchedDelimiter.length && (strMatchedDelimiter != strDelimiter)) arrData.push([]);
var strMatchedValue = arrMatches[2] ? arrMatches[2].replace(new RegExp( "\"\"", "g" ), "\"") : arrMatches[3];
arrData[ arrData.length - 1 ].push( strMatchedValue );
}
return arrData;
}
Bugfix when description is undefined (May 27 2015)
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [ {name: "Upload Rabobank bestand...", functionName: "upload"} ];
sheet.addMenu("Administratie", entries);
}
function upload() {
app = UiApp.createApplication().setTitle("Upload Rabobank transactions.txt");
form = app.createFormPanel().setId("frm").setEncoding("multipart/form-data");
panel = app.createVerticalPanel();
panel.add(app.createLabel("Ga naar www.rabobank.nl en download transactions.txt (nieuwe formaat) voor het juiste kwartaal. Bijvoorbeeld voor K3: 01-07-2012 t/m 30-09-2012."));
panel.add(app.createFileUpload().setName("thefile"));
panel.add(app.createSubmitButton("Upload"));
form.add(panel);
app.add(form);
SpreadsheetApp.getActiveSpreadsheet().show(app);
}
function doPost(e) {
/*Logger.log("TEST");
var files = DriveApp.getFilesByName("transactions.txt");
if (files.hasNext()) {
var file = files.next();
var blob = file.getAs(MimeType.PLAIN_TEXT);
csv = CSVToArray(blob.getDataAsString());
Logger.log(csv.length);
addTransactions(csv);
return;
} else {
Logger.log("no file");
return
}*/
var fileBlob = e.parameter.thefile;
csv = CSVToArray(fileBlob.contents);
Browser.msgBox("Klik op OK om te beginnen met het verwerken van " + csv.length + " transacties. Dit kan enkele minuten duren en geeft geen tussentijdse feedback.");
addTransactions(csv);
}
function addTransactions(csv) {
var inkomsten = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inkomsten"); ///getSheets()[2]; //getSheetByName("Inkomsten");
var uitgaven = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Uitgaven"); //getSheets()[1]; //getSheetByName("Uitgaven");
for (var i=0; i<csv.length; i++) {
Logger.log("Bezig met regel " + (i+1));
if (csv[i].length!=19) {
Logger.log("Fout op regel " + (i+1));
continue;
}
var year = csv[i][2].substr(0,4);
var month = csv[i][2].substr(4,2);
var day = csv[i][2].substr(6,2);
var isodate= year+"-"+month+"-"+day;
var quarter = "K"+Math.ceil(month/3);
var credit=(csv[i][3]=="C");
var amount=csv[i][4].replace(".",",");
var name=csv[i][6];
if (name==undefined) name="";
var invoicenr="";
var exvat=""; //=R[0]C[3]/(R[0]C[1]+1)";
var vatpct=""; //0;
var vatamt=""; //=R[0]C[-2]*R[0]C[-1]";
var category="";
var description = csv[i][10] || "";
if (csv[i][11]) description=description.trim()+" "+csv[i][11];
if (csv[i][12]) description=description.trim()+" "+csv[i][12];
if (csv[i][13]) description=description.trim()+" "+csv[i][13];
description = description.toLowerCase();
var sheet = credit ? inkomsten : uitgaven;
var nextRow = sheet.getLastRow()+1;
// Logger.log(description);
sheet.appendRow([isodate,quarter,invoicenr,name,description,exvat,vatpct,vatamt,amount,category]);
}
layoutSheet(inkomsten);
layoutSheet(uitgaven);
Browser.msgBox("Klaar met verwerken van " + csv.length + " transacties");
}
function layoutSheet(sheet) {
Logger.log(sheet.getName());
sheet.setFrozenRows(1);
sheet.setColumnWidth(1,80); //datum
sheet.setColumnWidth(2,50); //kwartaal
sheet.setColumnWidth(3,80); //factuur nr
sheet.setColumnWidth(4,250); //debiteur/crediteur
sheet.setColumnWidth(5,270); //omschrijving
sheet.setColumnWidth(6,70); //excl
sheet.setColumnWidth(7,70); //%
sheet.setColumnWidth(8,70); //btw bedrag
sheet.setColumnWidth(9,70); //inc
sheet.setColumnWidth(10,120); //soort
sheet.setColumnWidth(11,270); //soort
sheet.getRange("A:A").setNumberFormat("yyyy-MM-dd");
sheet.getRange("F:F").setNumberFormat("€ #,##0.00");
sheet.getRange("G:G").setNumberFormat("0.00%");
sheet.getRange("H:H").setNumberFormat("€ #,##0.00");
sheet.getRange("I:I").setNumberFormat("€ #,##0.00");
}
function CSVToArray(strData, strDelimiter) {
strDelimiter = (strDelimiter || ",");
var arrData = [[]];
var arrMatches = null;
var objPattern = new RegExp((
"(\" + strDelimiter + "|\r?\n|\r|^)" + // Delimiters.
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" + // Quoted fields.
"([^\"\" + strDelimiter + "\r\n]*))" // Standard fields.
),"gi");
while (arrMatches = objPattern.exec(strData)) {
var strMatchedDelimiter = arrMatches[1];
if (strMatchedDelimiter.length && (strMatchedDelimiter != strDelimiter)) arrData.push([]);
var strMatchedValue = arrMatches[2] ? arrMatches[2].replace(new RegExp( "\"\"", "g" ), "\"") : arrMatches[3];
arrData[ arrData.length - 1 ].push( strMatchedValue );
}
return arrData;
}