How Can We Help?

RSVP Report Google Sheet with API Access

You can enable API access to your RSVP Reports for use with your own scripts or this sample Google Sheets apps script.

Adding a script in Google Sheets

To use the script, access the Extensions menu in a blank Google Sheet and choose Apps Script. Then paste in the code shown below.

/*
1. Create a blank Google Sheet for your RSVP Reports
2. From the menu, choose Extensions, API Script
3. Paste in this code
4. Reload the browser tab for the Google Sheet. An RSVP Report menu will be displayed. 
5. The first time you run the RSVP Report, you will be prompted to enter the URL obtained from the bottom of the RSVP Report screen in WordPress
*/
function rsvpReport() {
  const setup = getOrCreateTab('Setup');
  let url = setup.getRange('B1').getValue();
  if(!url) {
    url = SpreadsheetApp.getUi().prompt('Enter the API access address from RSVPMaker');
    if(url) {
      setup.getRange('A1').setValue('API URL:');
      setup.getRange('B1').setValue(url);
    }
    else {
      SpreadsheetApp.getUi().alert('No API access address found');
      return;
    }  
  }
  const data = httpGet(url+'&time='+Math.random());
  Logger.log(data);
  Logger.log(data.fields);
  let title, rownumber;
  let lasttitle = '';
  data.rsvp.forEach(
    (datarow, dataindex) => {
      title = datarow.post_title+' '+datarow.date;
      Logger.log(title);
      if(title != lasttitle) {
      tab = getOrCreateTab(title, true);
      rownumber = 2;
      data.fields.forEach( (field, index) => { tab.getRange(1,index + 1).setValue(field); } );
      tab.setFrozenRows(1);
      Logger.log('new title '+title);
      }
      lasttitle = title;
        data.fields.forEach( (field, index) => { if(datarow[field])
          tab.getRange(rownumber,index + 1).setValue(datarow[field]);
          if(('owed' == field)) {
            if(datarow[field] > 0)
              tab.getRange(rownumber,index + 1).setFontColor('#FF0000');
            else
              tab.getRange(rownumber,index + 1).setFontColor('#000000');
          }
      } );
    rownumber++;
    }
  );
}

function backupReset() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const templateFile = DriveApp.getFileById(ss.getId());
  const date = new Date();
  const copy = templateFile.makeCopy('RSVP Report '+date.toLocaleString());
  const url = copy.getUrl();
  Logger.log(url);
  const setup = ss.getSheetByName('Setup');
  setup.getRange('A9').setValue('Backups');
  setup.getRange('A10').insertCells(SpreadsheetApp.Dimension.ROWS);
  var richValue = SpreadsheetApp.newRichTextValue()
  .setText(date.toLocaleString())
  .setLinkUrl(url)
  .build();
  setup.getRange('A10').setRichTextValue(richValue);
  const sheets = ss.getSheets();
  sheets.forEach(
    (sheet) => {
      if('Setup' != sheet.getName())
        ss.deleteSheet(sheet);
    }
  );
}

function httpGet(url, params, retries, nologging = false) {
    url = url.replace('country=ww','country=world');
    if(!nologging)
    if (params === void 0) { params = {}; }
    if (retries === void 0) { retries = 3; }
    var response; // eslint-disable-line @typescript-eslint/camelcase
    var data;
    try {
        response = UrlFetchApp.fetch(url, params);// (fullUrl);
    }
    catch (e) {
        if (retries > 0) {
            console.warn("Could not get data from API (" + retries + " attempts left) - ", e);
            return httpGet(url, params, retries - 1);
        }
        else {
          //// // Logger.log(url);
            console.error('Could not get data from API - ', e);
            return null;
        }
    }
    if('' == response) {
      //// // Logger.log('Empty response');
      return null; //httpGet(url, params, retries - 1);
    }

    console.log('httpGet: Retrieved data successfully');
    // // If server too busy, retry
    // if (response && response.getResponseCode() == 429 && retries > 0) {
    //     Utilities.sleep(500); // wait 0.5s before trying again
    //     return httpGet(url, params, retries - 1);
    // }
    try {
        data = JSON.parse(response.getContentText());
    }
    catch (e) {
        console.error('Could not parse JSON reply. Exception details: ' + e);
        if (retries > 0) {
        return httpGet(url, params, retries - 1);
        }
        else 
          return null;
    }
    return data;
}

function getOrCreateTab(tabname, clear = false, spreadsheet = null) {
  Logger.log('get or create '+tabname);
  if(!spreadsheet)
    spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.flush();
  var sheets = spreadsheet.getSheets();
  //// // Logger.log(tabname);
  var sheet = spreadsheet.getSheetByName(tabname);
  if(!sheet){
    spreadsheet.insertSheet(sheets.length);
    sheet = spreadsheet.getActiveSheet();
    sheet.setName(tabname);
    spreadsheet.getSheetByName(tabname).activate();
    //// // Logger.log('create sheet '+tabname);
  }
  else if (clear) {
    sheet.clearContents();
  }
  SpreadsheetApp.flush();
  return sheet;
}

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('RSVP Report')
    .addItem('Run Report', 'rsvpReport')
    .addItem('Backup and Reset', 'backupReset')
    .addToUi();
}

Reload the browser page or tab containing the Google Sheet and an RSVP Report menu will be displayed with 2 options, Run Report and Backup and Reset.

RSVP Report menu in Google Sheets

Enable API Access to Reports in WordPress

To enable API access, go to the bottom of the RSVP Report screen and click the link that says Enable API access.

The first time you run the RSVP Report function in Google Sheets, you will be prompted to authorize the script and then paste in that address.

Backup and Reset

When you use the Run Report function, a report for each upcoming event for which RSVPs are available will be added on a separate tab or sheet of your Google Sheets workbook.

Backup and Reset creates a dated copy of all that data and deletes all the worksheet tabs except for Setup, where your API access key and links to all backup copies are saved.

Google Sheets workbook after Backup and Reset has been run.

Click on the first backup link listed for the latest copy of your data.