sschwartzman · March 4, 2021 22:19
diff --git a/Google-Sheets-To-New-Relic-Insights.gs b/Google-Sheets-To-New-Relic-Insights.gs
 // Google Sheets to New Relic Insights
 // Google Apps Script that takes the contents of a Google Sheet (or any two-dimensional array)
 // and sends each row to New Relic as an Insights Event.

 // Configurable settings
 const InsightsInsertKey = 'your_insights_insert_api_key';
 const RpmAccountId = 'your_RPM_account_ID';
 const CSVDelimiter = ',';
 const DateFormats = ["M/D/YYYY", "M/D/YYYY h:mm:ss A"];

 // New Relic Insights settings (do not change)
 const MaxEvents = 1000;
 const MaxPayloadSize = 1000000;
 const MaxStringLength = 4096;
 const MaxAttributeNameLength = 255;
 const MaxAttributes = 255;
 const InsightsUrl = `https://insights-collector.newrelic.com/v1/accounts/${RpmAccountId}/events`;
 const InsightsOptions = {
    url: InsightsUrl,
    method: 'post',
    contentType: 'application/json',
    headers: {
        'Content-Type': 'application/json',
        'X-Insert-Key': InsightsInsertKey,
        'Content-Encoding': 'gzip',
        'Event-Count': 0
    }
 };

 // Enables this script to use moment.js
 eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.1/moment.min.js').getContentText());

 // main() is a simple example of how to get the sheet and data range you want to export to Insights
 // sendToInsights() is the actual function that accepts a two-dimensional array of data, such as Google Sheets offers up.

 function main () {
  // If running this from within the sheet you want to consume, leave fileId and sheetName blank.
  var fileId = 'your_spreadsheet_ID_here';
  var sheetName = 'your_sheet_name_here';

  if(isNotEmptyStr(fileId) && isNotEmptyStr(sheetName)) {
    var ss = SpreadsheetApp.openById(fileId);
    var sheet = ss.getSheetByName(sheetName);
  } else {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
  }
  
  sendToInsights(ss.getName(), sheet.getName(), sheet.getActiveRange());
 }

 function sendToInsights(filename, sheetname, data) {
  var eventCountRaw = 0;
  var eventCountSubmitted = 0;
  var payloadSize = 0;
  var events = [];
  var headers = [];
  var requests = [];
  
  var eventType = filename;
  if(filename !== sheetname) {
    eventType += `:${sheetname}`;
  }
  eventType = sanitizeName(eventType);
  Logger.log(`Batching events of eventType ${eventType} to send to Insights`);  
  
  for(record of data) {
    eventCountRaw++;
    if(eventCountRaw == 1) {
      for(key of record) {
        var key = sanitizeName(key);
        if(key.length > MaxAttributeNameLength) {
          key = key.slice(0, MaxAttributeNameLength);
        }
        headers.push(key);
      }
      continue;
    }
     
    if (events.length >= MaxEvents) {
      console.log(`Batch #${requests.length}: count: ${events.length}, size: ${payloadSize}`);
      requests.push(buildRequest(events));
      payloadSize = 0;
      events = [];
    }

    var event = {};
    for (var i = 0; i < record.length; i++) {
      if(event.length >= MaxAttributes) {
        break;
      }

      var value = record[i];
      if(isEmptyObj(value)) {
        continue;
      }

      var attr = headers[i];
      
      // Truncate long strings so they fit in Insights Events.
      if (value.length > MaxStringLength) {
        event[attr] = value.slice(0, MaxStringLength);
      } else {
        if (!isNaN(value)) {
          event[attr] = Number(value);
        } else {
          var dateCheck = moment(value.toString(), DateFormats, true);
          if (dateCheck.isValid()) {
            // Date --> Unix Timestamp for Insights to parse
            event[attr] = Number(dateCheck.unix());
            event[attr + '_CalendarQuarter'] = `${dateCheck.year().toString()}-Q${dateCheck.quarter().toString()}`;
          } else {
            event[attr] = value;
          }
        }
      }
    }

    // Once event is built-out, push event to stack to be processed, compute payload size.
    if(!isEmptyObj(event)) {
      eventCountSubmitted++;
      event.eventType = eventType;
      var newPayloadSize = JSON.stringify(event).length;

      // Batching the events into Insights-manageable chunks
      // At end of each loop, do so if event payload is maxed already.
      if ((newPayloadSize + payloadSize) >= MaxPayloadSize) {
        requests.push(buildRequest(events));
        console.log(`Batch #${requests.length}: count: ${events.length}, size: ${payloadSize}`);
        payloadSize = 0;
        events = [];
      }

      events.push(event);
      payloadSize += newPayloadSize;
    }
  }

  console.log(`Batch #${requests.length} (LAST BATCH): count: ${events.length}, size: ${payloadSize}`);
  requests.push(buildRequest(events));

  var responses = UrlFetchApp.fetchAll(requests);
  for(var r = 0; r < responses.length; r++) {
    var rcode = responses[r].getResponseCode();
    if(rcode < 200 || rcode >= 300) {
      Logger.log(`Batch #${r} post failed with HTTP code: ${rcode}, message: ${responses[r].getContentText()}`);
      eventCountSubmitted -= requests[r].headers['Event-Count'];
    } else {
      Logger.log(`Batch #${r} successfully posted to Insights.`);
    }
  }

  Logger.log(`Lines processed (including header): ${eventCountRaw}`);
  Logger.log(`Events recorded: ${eventCountSubmitted}`);
 }

 function buildRequest(body) {
  let request = JSON.parse(JSON.stringify(InsightsOptions));
  request.payload = Utilities.gzip(Utilities.newBlob(JSON.stringify(body)));
  request.headers['Event-Count'] = body.length;
  return request;
 }

 function isEmptyObj(obj) {
    for(var key in obj) {
        if(obj.hasOwnProperty(key))
            return false;
    }
    return true;
 }

 function isNotEmptyStr(str) {
    return (str && str.length > 0);
 }

 function sanitizeName(str) {
  return str.replace(/\s/g, '').replace(/-/g, '_').replace(/\&/g,"And");
 }
	// Google Sheets to New Relic Insights
	// Google Apps Script that takes the contents of a Google Sheet (or any two-dimensional array)
	// and sends each row to New Relic as an Insights Event.

	// Configurable settings
	const InsightsInsertKey = 'your_insights_insert_api_key';
	const RpmAccountId = 'your_RPM_account_ID';
	const CSVDelimiter = ',';
	const DateFormats = ["M/D/YYYY", "M/D/YYYY h:mm:ss A"];

	// New Relic Insights settings (do not change)
	const MaxEvents = 1000;
	const MaxPayloadSize = 1000000;
	const MaxStringLength = 4096;
	const MaxAttributeNameLength = 255;
	const MaxAttributes = 255;
	const InsightsUrl = `https://insights-collector.newrelic.com/v1/accounts/${RpmAccountId}/events`;
	const InsightsOptions = {
	url: InsightsUrl,
	method: 'post',
	contentType: 'application/json',
	headers: {
	'Content-Type': 'application/json',
	'X-Insert-Key': InsightsInsertKey,
	'Content-Encoding': 'gzip',
	'Event-Count': 0
	}
	};

	// Enables this script to use moment.js
	eval(UrlFetchApp.fetch('https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.1/moment.min.js').getContentText());

	// main() is a simple example of how to get the sheet and data range you want to export to Insights
	// sendToInsights() is the actual function that accepts a two-dimensional array of data, such as Google Sheets offers up.

	function main () {
	// If running this from within the sheet you want to consume, leave fileId and sheetName blank.
	var fileId = 'your_spreadsheet_ID_here';
	var sheetName = 'your_sheet_name_here';

	if(isNotEmptyStr(fileId) && isNotEmptyStr(sheetName)) {
	var ss = SpreadsheetApp.openById(fileId);
	var sheet = ss.getSheetByName(sheetName);
	} else {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = ss.getActiveSheet();
	}

	sendToInsights(ss.getName(), sheet.getName(), sheet.getActiveRange());
	}

	function sendToInsights(filename, sheetname, data) {
	var eventCountRaw = 0;
	var eventCountSubmitted = 0;
	var payloadSize = 0;
	var events = [];
	var headers = [];
	var requests = [];

	var eventType = filename;
	if(filename !== sheetname) {
	eventType += `:${sheetname}`;
	}
	eventType = sanitizeName(eventType);
	Logger.log(`Batching events of eventType ${eventType} to send to Insights`);

	for(record of data) {
	eventCountRaw++;
	if(eventCountRaw == 1) {
	for(key of record) {
	var key = sanitizeName(key);
	if(key.length > MaxAttributeNameLength) {
	key = key.slice(0, MaxAttributeNameLength);
	}
	headers.push(key);
	}
	continue;
	}

	if (events.length >= MaxEvents) {
	console.log(`Batch #${requests.length}: count: ${events.length}, size: ${payloadSize}`);
	requests.push(buildRequest(events));
	payloadSize = 0;
	events = [];
	}

	var event = {};
	for (var i = 0; i < record.length; i++) {
	if(event.length >= MaxAttributes) {
	break;
	}

	var value = record[i];
	if(isEmptyObj(value)) {
	continue;
	}

	var attr = headers[i];

	// Truncate long strings so they fit in Insights Events.
	if (value.length > MaxStringLength) {
	event[attr] = value.slice(0, MaxStringLength);
	} else {
	if (!isNaN(value)) {
	event[attr] = Number(value);
	} else {
	var dateCheck = moment(value.toString(), DateFormats, true);
	if (dateCheck.isValid()) {
	// Date --> Unix Timestamp for Insights to parse
	event[attr] = Number(dateCheck.unix());
	event[attr + '_CalendarQuarter'] = `${dateCheck.year().toString()}-Q${dateCheck.quarter().toString()}`;
	} else {
	event[attr] = value;
	}
	}
	}
	}

	// Once event is built-out, push event to stack to be processed, compute payload size.
	if(!isEmptyObj(event)) {
	eventCountSubmitted++;
	event.eventType = eventType;
	var newPayloadSize = JSON.stringify(event).length;

	// Batching the events into Insights-manageable chunks
	// At end of each loop, do so if event payload is maxed already.
	if ((newPayloadSize + payloadSize) >= MaxPayloadSize) {
	requests.push(buildRequest(events));
	console.log(`Batch #${requests.length}: count: ${events.length}, size: ${payloadSize}`);
	payloadSize = 0;
	events = [];
	}

	events.push(event);
	payloadSize += newPayloadSize;
	}
	}

	console.log(`Batch #${requests.length} (LAST BATCH): count: ${events.length}, size: ${payloadSize}`);
	requests.push(buildRequest(events));

	var responses = UrlFetchApp.fetchAll(requests);
	for(var r = 0; r < responses.length; r++) {
	var rcode = responses[r].getResponseCode();
	if(rcode < 200 \|\| rcode >= 300) {
	Logger.log(`Batch #${r} post failed with HTTP code: ${rcode}, message: ${responses[r].getContentText()}`);
	eventCountSubmitted -= requests[r].headers['Event-Count'];
	} else {
	Logger.log(`Batch #${r} successfully posted to Insights.`);
	}
	}

	Logger.log(`Lines processed (including header): ${eventCountRaw}`);
	Logger.log(`Events recorded: ${eventCountSubmitted}`);
	}

	function buildRequest(body) {
	let request = JSON.parse(JSON.stringify(InsightsOptions));
	request.payload = Utilities.gzip(Utilities.newBlob(JSON.stringify(body)));
	request.headers['Event-Count'] = body.length;
	return request;
	}

	function isEmptyObj(obj) {
	for(var key in obj) {
	if(obj.hasOwnProperty(key))
	return false;
	}
	return true;
	}

	function isNotEmptyStr(str) {
	return (str && str.length > 0);
	}

	function sanitizeName(str) {
	return str.replace(/\s/g, '').replace(/-/g, '_').replace(/\&/g,"And");
	}