The WriteJSONtoSheet method take a JSON object and appends it as a new row in a Google Spreadsheet. It takes the keys of the JSON object, matches them with the header row of the spreadsheet and arranges the columns accordingly. If there’s no column corresponding to a key, it is automatically added. This is handy for logging operations and data objects in a Google Sheet for easy analysis.
// Written by Amit Agarwal www.ctrlq.org function writeJSONtoSheet(json) { var sheet = SpreadsheetApp.getActiveSheet(); var keys = Object.keys(json).sort(); var last = sheet.getLastColumn(); var header = sheet.getRange(1, 1, 1, last).getValues()[0]; var newCols = []; for (var k = 0; k < keys.length; k++) { if (header.indexOf(keys[k]) === -1) { newCols.push(keys[k]); } } if (newCols.length > 0) { sheet.insertColumnsAfter(last, newCols.length); sheet.getRange(1, last + 1, 1, newCols.length).setValues([newCols]); header = header.concat(newCols); } var row = []; for (var h = 0; h < header.length; h++) { row.push(header[h] in json ? json[header[h]] : ""); } sheet.appendRow(row); }