Markdown Calendar Table Generator Using URL Parameters and Google Sheets/Apps Script

Share & showcase
,
1

There are already a lot of great posts showcasing markdown table generators for calendars using python scripts, Alfred, AutoHotKey, TextExpander, etc.

I switch computers often and wanted a cross platform approach that was quick easy to setup.

I started with a Google Sheet that takes an input for Year and Month:

PULBIC GSHEET - VIEW AND MAKE A COPY

MD_Cal - Google Sheets

Screen Shot 2020-09-06 at 10.13.41 PM

Then I setup an Apps Script that returns the calendar range after setting the Year and Month via URL parameters:

  • View Sheet, Make a Copy
  • Copy script from Summary
Summary 

function doGet(e) {
  var viewPOST = {};

  viewPOST = e.parameters;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Calendar');
  var y  = viewPOST.y;
  var m  = viewPOST.m;
  
  sh.getRange(2, 4).setValue(y);
  sh.getRange(2, 8).setValue(m);
  
  var md = [];
  md.push(sh.getRange('B10:P10').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B11:P11').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B12:P12').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B13:P13').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B14:P14').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B15:P15').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B15:P15').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B16:P16').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B17:P17').getValues().toString().replace(/,/g,''));
  md.push(sh.getRange('B18:P18').getValues().toString().replace(/,/g,''));
  
  md = md.toString().replace(/,/g,'\r');

  return ContentService.createTextOutput(md).setMimeType(ContentService.MimeType.csv);   
  
}
  • Paste into Tools>Script Editor
  • Save script and publish as webapp. Note the new URL.
  • Replace {script_id} with your new script’s id from the URL.
  • Replace y and m with values for Year and month, using YYYY and D format.

The base URL of the Apps Script web service can now be appended with the desired Year and Month to generate a markdown table.

https://script.google.com/a/greenflux.us/macros/s/{`script_id`}/exec?y=2020&m=9

- From there you can copy/paste the text into a note, or publish the sheet and embed the public URL.

Screen Shot 2020-09-06 at 10.34.42 PM

# SEP 2020
|SU|MO|TU|WE|TH|FR|SA|
|-|-|-|-|-|-|-|
|30|31|01|02|03|04|05|
|06|07|08|09|10|11|12|
|13|14|15|16|17|18|19|
|13|14|15|16|17|18|19|
|20|21|22|23|24|25|26|
|27|28|29|30|01|02|03|
|04|05|06|07|08|09|10|

Rendered in Obsidian Preview:

Screen Shot 2020-09-06 at 10.32.47 PM

I just started on this today and wanted to share. :smile:

There are several issues that need to be addressed in the Apps Script code (expand Summary section, above).

  • Hiding 6th/7th weeks when not needed
  • Days not in this month should be italics or greyed out
  • I’m tired
  • Could use option for week starting on Sun or Mon

[EDIT] I feel like there’s a lot of redundancy in the Apps Script that could be simplified/reduced. Any suggestions?

4 Likes
2

It is already finished.

1 Like