I created a javascript code which, on the click of a button, sends some data to a Google Spreadsheet. This works.
However, when I want to add a formula formulated within a ARRAYFORMULA() function, this stops working.
The formula isn't that complicated, and is added to $L2 (where there's already a row of data):
=ARRAYFORMULA(IF(ISBLANK($K$2:$K);"";split($K$2:$K;"/")))
And when checking in the project view within script.google.com, the data transfer was succesful. It just isn't loaded into the spreadsheet.
When removing the =ARRAYFORMULA() part of the formula and adding it to the first row on $L2, the scripts work fine and data is added whenever I push the button.
So this works:
=IF(ISBLANK($K$2:$K);"";split($K$2:$K;"/"))
However this requires me to copy the code to new rows, which will be a hassle when more columns require formulas.
As for the Google Script, I've used a (modified version) of Martin Hawksey's script (so that double entries based on column C are ignored: eventually I want double entries to be overwritten but I've not figured that out yet) but using Hawksey's original doesn't make a difference in terms of this issue.
function doPost(e) {
try {
Logger.log(e); // the Google Script version of console.log see: Class Logger
record_data(e);
// names and order of form elements (if set)
var orderParameter = e.parameters.formDataNameOrder;
var dataOrder;
if (orderParameter) {
dataOrder = JSON.parse(orderParameter);
}
return ContentService // return json success results
.createTextOutput(
JSON.stringify({"result":"success",
"data": JSON.stringify(e.parameters) }))
.setMimeType(ContentService.MimeType.JSON);
} catch(error) { // if error return this
Logger.log(error);
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": error}))
.setMimeType(ContentService.MimeType.JSON);
}
}
/**
* record_data inserts the data received from the html form submission
* e is the data received from the POST
*/
function record_data(e) {
var lock = LockService.getDocumentLock();
lock.waitLock(30000); // hold off up to 30 sec to avoid concurrent writing
try {
Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
// select the 'responses' sheet by default
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = e.parameters.formGoogleSheetName || "responses";
var sheet = doc.getSheetByName(sheetName);
var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var newHeader = oldHeader.slice();
var fieldsFromForm = getDataColumns(e.parameters);
var row = [new Date()]; // first element in the row should always be a timestamp
// loop through the header columns
for (var i = 1; i < oldHeader.length; i++) { // start at 1 to avoid Timestamp column
var field = oldHeader[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
// mark as stored by removing from form fields
var formIndex = fieldsFromForm.indexOf(field);
if (formIndex > -1) {
fieldsFromForm.splice(formIndex, 1);
}
}
// set any new fields in our form
for (var i = 0; i < fieldsFromForm.length; i++) {
var field = fieldsFromForm[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
newHeader.push(field);
}
var exists = sheet
.getDataRange()
.getValues()
.map(function(r) {
Logger.log(r);
return r[2];
})
.indexOf(row[2]) > -1
//row.push(exists);
if (!exists) {
// more efficient to set values as [][] array than individually
var nextRow = sheet.getLastRow() + 1; // get next row
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// update header row with any new data
if (newHeader.length > oldHeader.length) {
sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
}
}
}
catch(error) {
Logger.log(error);
}
finally {
lock.releaseLock();
return;
}
}
function getDataColumns(data) {
return Object.keys(data).filter(function(column) {
return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
});
}
function getFieldFromData(field, data) {
var values = data[field] || '';
var output = values.join ? values.join(', ') : values;
return output;
}
The original code -which doesn't work with ARRAYFORMULA() either-:
function doPost(e) {
try {
Logger.log(e); // the Google Script version of console.log see: Class Logger
record_data(e);
// names and order of form elements (if set)
var orderParameter = e.parameters.formDataNameOrder;
var dataOrder;
if (orderParameter) {
dataOrder = JSON.parse(orderParameter);
}
return ContentService // return json success results
.createTextOutput(
JSON.stringify({"result":"success",
"data": JSON.stringify(e.parameters) }))
.setMimeType(ContentService.MimeType.JSON);
} catch(error) { // if error return this
Logger.log(error);
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": error}))
.setMimeType(ContentService.MimeType.JSON);
}
}
/**
* record_data inserts the data received from the html form submission
* e is the data received from the POST
*/
function record_data(e) {
var lock = LockService.getDocumentLock();
lock.waitLock(30000); // hold off up to 30 sec to avoid concurrent writing
try {
Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
// select the 'responses' sheet by default
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = e.parameters.formGoogleSheetName || "responses";
var sheet = doc.getSheetByName(sheetName);
var oldHeader = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var newHeader = oldHeader.slice();
var fieldsFromForm = getDataColumns(e.parameters);
var row = [new Date()]; // first element in the row should always be a timestamp
// loop through the header columns
for (var i = 1; i < oldHeader.length; i++) { // start at 1 to avoid Timestamp column
var field = oldHeader[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
// mark as stored by removing from form fields
var formIndex = fieldsFromForm.indexOf(field);
if (formIndex > -1) {
fieldsFromForm.splice(formIndex, 1);
}
}
// set any new fields in our form
for (var i = 0; i < fieldsFromForm.length; i++) {
var field = fieldsFromForm[i];
var output = getFieldFromData(field, e.parameters);
row.push(output);
newHeader.push(field);
}
// more efficient to set values as [][] array than individually
var nextRow = sheet.getLastRow() + 1; // get next row
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// update header row with any new data
if (newHeader.length > oldHeader.length) {
sheet.getRange(1, 1, 1, newHeader.length).setValues([newHeader]);
}
}
catch(error) {
Logger.log(error);
}
finally {
lock.releaseLock();
return;
}
}
function getDataColumns(data) {
return Object.keys(data).filter(function(column) {
return !(column === 'formDataNameOrder' || column === 'formGoogleSheetName' || column === 'formGoogleSendEmail' || column === 'honeypot');
});
}
function getFieldFromData(field, data) {
var values = data[field] || '';
var output = values.join ? values.join(', ') : values;
return output;
}
Link to the Google Spreadsheet I've used to test this. I've added two sheets: one with the ARRAYFORMULA() and one without (just the regular formula), so you can test with the same data I've got. Also, the script seems to automatically add a date, is there a way to remove this? I don't need a date.
The authentication should be set up correctly (since everything else seems to work) and as I've said, the data seems to be succesfully transferred to Google: just not into the actual rows. I can't see anything wrong in the console, but I must admit I'm not that fluent in google-coding, haha.
The javascript code used:
function sendData(e) {
var url = 'https://script.google.com/macros/s/[url]/exec';
var xhr = new XMLHttpRequest();
xhr.open('POST', url);
xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
xhr.onreadystatechange = function() {
if (xhr.readyState === 4 && xhr.status === 200) {
console.log('done?');
}
};
// load variables into data array
var data = {'Naam': naam, 'Ras': ras, 'Geslacht': geslacht, 'Dressuur': dressuur, 'Springen': springen, 'Western': western, 'Mennen': mennen, 'GI': gi};
var encoded = Object.keys(data).map(function(k) {
return encodeURIComponent(k) + "=" + encodeURIComponent(data[k]);
}).join('&');
xhr.send(encoded);
}
So, to sum up my question:
How to incorporate formulas formulated with =ARRAYFORMULA() into a sheet which automatically adds new rows when clicked on a website-button?
