"Integrate" G-suite Sheets, Google scripts and SMF / mySQL, search, download etc

Started by PoML, May 12, 2020, 12:50:39 PM

Previous topic - Next topic



Some of you may know of Googles offer to give away G-suite to registered non-profits .
My organisation did just that, and had partly used SMF as the memberlist for the organisation.

I was about to use zapier, to set up integration but found that I could do everything without opening up that much for another company's software.  These are the few tips I used to make a "dashboard" for our membership responsible using Visma eAccounting, Scripts, URLs and Google Sheets:

1) URL search to lookup profile based on membergroup, name, email etc:
I discovered that even the member search can be put to good use by options on the URL:
So "any" search in the forum for members can be accomplished by having a lookup in a field and opening the following URL:

Also made a dashboard in Google Sheet to summarise information on a member and create links to update information like payment, email etc.

2) Created a script that downloads fields from "members" and additional fields to a new Sheet tab.
Added it to a custom menu in Google Sheets.  (script enclosed below if it is of help for anybody).

3) Created a Script for membercards to be mailed.
This script
* go through all members that have paid,
* create a membercard with QR-code to check validity vs profile (QR-code is an URL into the forum and can be read by ie Google Lens)
* export the card to PDF
* attach the PDF to an email.
* Lookup the email address of the member
* add the email to gmail "drafts" folder
(not enclosed here, as I think it is a bit too specific for use, but feel free to ask)

Google Script code for use:

var server = 'mysql.mydomain.com';
var port = 3306;
var dbName = 'name of database';
var username = 'log in user;
var password = 'password';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;

function readData() {
  var conn = Jdbc.getConnection(url, username, password);
  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT a.*, b.value AS betalt, c.value AS fetlife, d.value AS loca, e.value AS gender, f.value AS gpdr_policydate, g.value AS about, h.value AS skype   FROM smf_members AS a   LEFT JOIN smf_themes AS b  ON b.id_member = a.id_member and b.variable ="cust_betalt"   LEFT JOIN smf_themes AS c  ON c.id_member = a.id_member and c.variable ="cust_fetlif"   LEFT JOIN smf_themes AS d  ON d.id_member = a.id_member and d.variable ="cust_loca"   LEFT JOIN smf_themes AS e  ON e.id_member = a.id_member and e.variable ="cust_gender"   LEFT JOIN smf_themes AS f  ON f.id_member = a.id_member and f.variable ="gpdr_policydate"   LEFT JOIN smf_themes AS g  ON g.id_member = a.id_member and g.variable ="about"   LEFT JOIN smf_themes AS h  ON h.id_member = a.id_member and h.variable ="cust_skype"   ORDER by id_member');
  var metaData=results.getMetaData();
  var numCols = metaData.getColumnCount();
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('members');
  var arr=[];

  for (var col = 0; col < numCols; col++) {
    arr.push(metaData.getColumnName(col + 1));
  /* fix bug that all headings are named "value" instead of the AS header from SQL */
  arr[numCols-7] = "betalt";
  arr[numCols-6] = "fetlife";
  arr[numCols-5] = "loca";
  arr[numCols-4] = "gender";
  arr[numCols-3] = "gpdr_policydate";
  arr[numCols-2] = "about";
  arr[numCols-1] = "skype";   


while (results.next()) {
  for (var col = 0; col < numCols; col++) {
    arr.push(results.getString(col + 1));