Search code examples
javascriptgoogle-apps-scriptgoogle-sheets-formula

How do I store data from month to month in Google App Script?

I have an app script that is setup to a trigger to automatically send out the statistics on the 1st of each month. I currently store the data using PropertiesService() class in name value pairs. The variables I need to store are as follows

  • Month
  • Total Referrals
  • Intakes Complete
  • Intakes Returned
  • Completed Vs Returned

Here is a coding examples:

const scriptProperties = PropertiesService.getScriptProperties(); scriptProperties.setProperties({ 'month': monthName, 'totalReferrals': totalReferrals, 'intakesComplete': intakesComplete, 'ssmReturns': ssmReturns, 'completedVsReturned': completedvsReturnedFormatted });

I pull the data like this:

const userProperties = PropertiesService.getScriptProperties(); const units = userProperties.getProperty('totalReferrals'); console.log('values of units %s', units);

It logs just fine. How can I save the data from the last month (Example: September) when the email is sent out on the first of the month and pull that data to send out the new statistics for October. The goal is to compare the two months together. The data it's pulling from is constantly changing and the data on October 1st will be different on October 2nd.

Solution

  • Use today's date as top level key and stringify the rest of the values:

    const dateAsStr = Intl.DateTimeFormat('en-US').format(new Date());
const scriptProperties = PropertiesService.getScriptProperties(); 
scriptProperties.setProperty(dateAsStr, JSON.stringify({ 'month': monthName, 'totalReferrals': totalReferrals, 'intakesComplete': intakesComplete, 'ssmReturns': ssmReturns, 'completedVsReturned': completedvsReturnedFormatted }));

    To retrieve,

    /*Oct 1 2024 date key*/
const date20241001AsStr = Intl.DateTimeFormat('en-US').format(new Date(2024,09/*month starts from 0*/,1 ));
const scriptProperties = PropertiesService.getScriptProperties(); 
const storedData = JSON.Parse(scriptProperties.getProperty(date20241001AsStr));

    Do pay special attention to properties service storage quota and other related properties service quotas: https://developers.google.com/apps-script/guides/services/quotas#current_limitations