Advertisement:

Author Topic: "Integrate" G-suite Sheets, Google scripts and SMF / mySQL, search, download etc  (Read 1103 times)

Offline PoML

  • Semi-Newbie
  • *
  • Posts: 46
  • I am the Norwegian translation team
Hi!

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:
https://www/forum/index.php?action=mlist;sa=search;fields=cust_cust_betalt,name,email,member,group;search=TEXT-TO-SEARCH

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:

Code: [Select]
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');
  sheet.clearContents();
  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";   
 

  sheet.appendRow(arr);

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

results.close();
stmt.close();
}