Hi @taylordowns2000 @aleksa-krolls – I’m a rank amateur at this, but I believe I’ve created a useful option for folks looking to rapidly ingest unpredictable Google Sheets data (such as here). Take a look and let me know if you think it’s useful, or any feedback that pops out.
I based on it on this template, but I adjusted for a different use case: Instead of grabbing data from a predictable form fill, the intent here is to enable bulk ingestion of spreadsheet data to OFn that the user hasn’t built a specific pipeline for yet. Therefore, the ingestion script discovers the field names from the first row of the sheet. I also consolidated all data for a particular row/record into an “allOneString” field, so that the user has a record of all the messy Sheets data, even if s/he only maps a few of the fields to a final destination in OFn. Finally, I let the user set a start/stop row in case they don’t want to ingest all rows in the data.
Admittedly, two of the functions in your original template confused me, so I’m not using them, but I left them in the code commented out in case I want them later.
function getReportData() {
///////////////////
// Hardcode THESE VALUES:
// sheetId, startRow, lastRow
///////////////////
var sheetData = { sheetId: '0', openFn: 'GSheet data', recordArray: [] };
startRow = 2;
lastRow = 999999;
///////////////////
///////////////////
// Gather data, prepare variables
var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues();
var ss = SpreadsheetApp.getActiveSheet();
var lastColumn = ss.getLastColumn();
var bottomRow = ss.getLastRow();
var columnNames = [];
// Get max row value (bottomRow vs. lastRow)
if (bottomRow > lastRow) {bottomRow = lastRow;}
// Pull column names from first row
c = 0;
while (c < lastColumn) {
columnNames.push(data[0][c])
c++
};
// Loop through each row of data (from startRow to bottomRow) from the sheet, create sheetData.recordArray[k] data objects.
k = 0;
for (let i = startRow-1; i < bottomRow; i++) {
// Skip header row
if (i == 0) {continue;}
// Old code from openFn. May be useful at some point...
// if (isDate(data[i][0])) {
// // ignore header line
// if (isNumber(data[i][3])) {
// pageCount = data[i][3];
// }
// if (isNumber(data[i][6])) {
// bookRating = data[i][6];
// }}
sheetData.recordArray[k] = {};
for (c in columnNames) {
sheetData.recordArray[k][columnNames[c]] = data[i][c];
}
sheetData.recordArray[k]['allOneString'] = JSON.stringify(sheetData.recordArray[k])
k++;
}
sendToOpenFn(sheetData);
}
function sendToOpenFn(e) {
var payload = JSON.stringify(e);
var url = 'https://www.openfn.org/inbox/your-id-here';
var options = {
method: 'post',
contentType: 'application/json',
payload: payload,
};
var response = UrlFetchApp.fetch(url, options);
}
// Old code from openFn. May be useful at some point...
// function isDate(v) {
// if (Object.prototype.toString.call(v) === '[object Date]') {
// if (isNaN(v.getTime())) {
// return false;
// } else {
// return true;
// }
// } else {
// return false;
// }
// }
// function isNumber(v) {
// if (Object.prototype.toString.call(v) === '[object Number]') {
// return true;
// } else {
// return false;
// }
// }
// Logger.log(sheetData);