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:
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();
}