User Tools

Site Tools


Sidebar



docs

Google Docs

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

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;
}
docs.txt · Last modified: 2016/11/23 11:26 by rick