Skip to content

Instantly share code, notes, and snippets.

View mrlynn's full-sized avatar
🌐
Philadelphia, PA

Michael Lynn mrlynn

🌐
Philadelphia, PA
View GitHub Profile
[
{
$match: {
rated: "TV-PG",
},
},
{
$match:
/**
* query: The query in MQL.
@mrlynn
mrlynn / gist:a58110cd668e45076cd0c3848cdaee4a
Created September 1, 2022 16:37 — forked from johnlpage/gist:2e8bd55ed195cccd4af7cea718f1c640
Examples for MongoDB Atlas Data API Video
ENDPOINT
--------
https://data.mongodb-api.com/app/data-amzuu/endpoint/data/beta
API KEY
--------
0vaT8d5Vh9cgvm3KdIQJWkl5M8alZgnoOczmApFlWVTMqisg24QWrUfMS0wkQ5Sj
@mrlynn
mrlynn / gist:48ef32e6ca960b2b5796a98c8ca0daaa
Last active December 2, 2020 20:57
Get Fiscal Year from Date in Google App Script - FY begins in Feb
function fyQ(date) {
var d = new Date(date);
var month = d.getMonth()+1;
var year = d.getFullYear();
// FY
if (month > 1) {
var year = year + 1
}
Logger.log("Year: " + year + " month: " + month)
@mrlynn
mrlynn / fetchData.js
Created April 5, 2020 15:22
Query MongoDB Stitch API for Data from A Google Apps Script - maintaining timeout to avoid overloading API
/**
|* Insert this code into your Google Sheets Script Editor
|* From there, you can insert into a cell in the sheet =fetchCode("somevalue")
|* This will return the value fetched from the database and set a last date fetched.
|* Next time you want to fetch - it will compare the last date fetched. If it's more than a day
|* It will fetch again - otherwise, not.
**/
function fetchCode(code) {
if (!code || code == "") {
@mrlynn
mrlynn / lookupTwilio.js
Last active April 20, 2019 09:27
MongoDB Stitch Function to Lookup a number using Twilio's lookup API
exports = function(phoneNumber){
const host = "lookups.twilio.com";
const path = `/v1/PhoneNumbers/${phoneNumber}`;
const lookupUrl = `https://lookups.twilio.com/v1/PhoneNumbers/${phoneNumber}`;
const http = context.services.get("http");
const { SID, Secret } = context.values.get("twilioCredentials");
return http.get({
"scheme": "https",
host,
path,
@mrlynn
mrlynn / OnOpen.js
Created March 3, 2019 16:05
Google Sheet Script to Create a Menu Item OnOpen - part of Stitching Sheets blog article
/****
* This function runs automatically and adds a menu item to Google Sheets
****/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
sheet.setActiveSheet(sheet.getSheetByName("Events"));
var entries = [{
name : "Export Events to MongoDB",
functionName : "exportEventsToMongoDB"
},{
@mrlynn
mrlynn / StitchingSheets.js
Last active October 25, 2023 17:50
Google Sheets Script - Complete Script for Article Stitching Sheets
/****
* Michael Lynn - http://blog.mlynn.org
* Stitching Sheets - Integrating Google Sheets with MongoDB Using MongoDB Stitch
****/
// Create an object which contains keys for each column in the spreadsheet
var columns = { // 0 indexed
type: 2,
date_start: 3,
date_end: 4,
@mrlynn
mrlynn / removeEventFromMongoDB.js
Created March 3, 2019 13:03
Google Sheets Script to Remove Events from a MongoDB Database
/****
* Delete the events from the Calendar and remover the eventID Reference from the sheet - wipeout.
****/
function removeEventsFromMongoDB() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Events");
var headerRows = 1; // Number of rows of header info (to skip)
var range = sheet.getDataRange();
var numRows = range.getNumRows();
var data = range.getValues();
@mrlynn
mrlynn / importGoogleSheetsEvent.js
Created March 3, 2019 11:46
Receive POST from Google Sheets and Insert into MongoDB Database
exports = async function(payload) {
const mongodb = context.services.get("mongodb-atlas");
const eventsdb = mongodb.db("events");
const eventscoll = eventsdb.collection("events");
const result= await eventscoll.insertOne(payload.query);
var id = result.insertedId.toString();
if(result) {
return JSON.stringify(id,false,false);
}
return { text: `Error saving` };
@mrlynn
mrlynn / exportEventsToMongoDB.js
Last active March 9, 2024 10:11
Google Sheets Script to Export Data to MongoDB Stitch API Service
/****
* Export the events from the sheet to a MongoDB Database via Stitch
****/
function exportEventsToMongoDB() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Events");
var headerRows = 1; // Number of rows of header info (to skip)
var range = sheet.getDataRange(); // determine the range of populated data
var numRows = range.getNumRows(); // get the number of rows in the range
var data = range.getValues(); // get the actual data in an array data[row][column]