What about doing the calculation when a new record is posted, and having it returned to the app immediately ? You could do this in the script, and not make a mess of your data ?
Yes, is a possibility.
I could try how it works and see if it works well.
But there is a problem: I am not able to create scripts
I will have a go at making one for you.
Please confirm the calculations you want on which columns for the last two entries.
if you give me an example of calculation and teach me how to modify, I will customize the calculations.
To start the calculation examples are:
J16=(I16/(B16-B15))(C16-C15)
K16=(I16/(B16-B15))(D16-D15)
If you want to give me some simple examples of operations (+, -, /, *) so I understand how the script works.
Thanks
Working off your spreadsheet data above, are these the values you would be expecting?
J16 = 41.9244
K16 = 14.5824
Try using lines 14-15.
The result you wrote to me cannot come as you are missing some data: I16, B16, C16 and D16.
I did, I used the last two lines
Doing that and your equation will ALWAYS = 0 (zero)
It will always be 0 so that I don't enter data in the cells B16, C16, D16 and I16.
Another solution could be to insert directly into cells J16 and K16 the formula "I16 / (B16-B15)) (D16-D15)" so that the spreadsheet of gsheet. But the row number increases every time I insert a new record.
For example, insert the formula as text inside the cell.
I am now completely confused 
My understanding was that you wanted to run calculations on the last two rows (the ones with data in them already) In your case this would be rows 14 and 15 with this data:
running your formulas returns:
41.9244
14.5824
I have written a script for you to add to your "CREATE" function that will do the calculation on the last two lines of the data, after you add a new row, and then return the results to your app.
// Create/Add new record, using comma separated values
if ( fn == 'CREATE' ) {
var data = e.parameter.DATA.split(',');
sh.appendRow(data);
var lr = sh.getLastRow();
var lrdata = sh.getRange(lr-1,1,2,9).getValues();
var jData = ((lrdata[1][8] / (lrdata[1][1] - lrdata[0][1])) * (lrdata[1][2] - lrdata[0][2])).toFixed(2);
var kData = ((lrdata[1][8] / (lrdata[1][1] - lrdata[0][1])) * (lrdata[1][3] - lrdata[0][3])).toFixed(2);
return ContentService.createTextOutput(JSON.stringify("New record created, " + jData + ", " + kData);
}
I try to insert your script inside the one I am using. Where do I post it?
I am attaching the scrip I am using now.
function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var fn = e.parameter.FN;
var rg = sh.getDataRange().getValues();
// Create/Add new record, using comma separated values
if ( fn == 'CREATE' ) {
var data = e.parameter.DATA.split(',');
sh.appendRow(data);
return ContentService.createTextOutput("New record created");
}
// Reads/Returns all data as a stringified JSON List
else if ( fn == 'READ' ) {
return ContentService.createTextOutput(JSON.stringify(rg));
}
// Edit/Update existing record, requires index/row and current col1 to match
else if ( fn == 'UPDATE' ) {
var index = e.parameter.INDEX; //index in list
var col1 = e.parameter.COL1; // current/existing value of col1 - it could be replaced...
var data = e.parameter.DATA.split(','); //new data
var range = sh.getRange((parseInt(index)+1),1,1,data.length);
for (var i = 0; i < rg.length; i++ ) {
if ( index != undefined && i == index && col1 == rg[i][0] ) {
range.setValues([data]);
}
}
return ContentService.createTextOutput("Record updated");
}
// deletes a single record (and its row) from sheet. Requires row index and col1 to match
else if ( fn == 'DELETE' ) {
var index = e.parameter.INDEX; //index in list
var col1 = e.parameter.COL1; // current/existing value of col1 - it could be replaced...
for (var i = 0; i < rg.length; i++ ) {
if ( index != undefined && i == index && col1 == rg[i][0] ) {
sh.deleteRow(parseInt(index)+1);
}
}
return ContentService.createTextOutput("Existing record deleted");
}
// outputs results from SQL query of all data
else if ( fn == 'QUERY' ) {
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = e.parameter.SQL;
var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
return ContentService.createTextOutput(JSON.stringify(getResult));
}
}I modified the script:
function doGet(e) {
var ss = SpreadsheetApp.openById(e.parameter.ID);
var sh = ss.getSheetByName(e.parameter.SH);
var fn = e.parameter.FN;
var rg = sh.getDataRange().getValues();
if ( fn == 'CREATE' ) {
var data = e.parameter.DATA.split(',');
sh.appendRow(data);
var lr = sh.getLastRow();
var lrdata = sh.getRange(lr-1,1,2,9).getValues();
var jData = ((lrdata[1][8] / (lrdata[1][1] - lrdata[0][1])) * (lrdata[1][2] - lrdata[0][2])).toFixed(2);
var kData = ((lrdata[1][8] / (lrdata[1][1] - lrdata[0][1])) * (lrdata[1][3] - lrdata[0][3])).toFixed(2);
return ContentService.createTextOutput(JSON.stringify("New record created, " + jData + ", " + kData));
}
// Reads/Returns all data as a stringified JSON List
else if ( fn == 'READ' ) {
return ContentService.createTextOutput(JSON.stringify(rg));
}
// Edit/Update existing record, requires index/row and current col1 to match
else if ( fn == 'UPDATE' ) {
var index = e.parameter.INDEX; //index in list
var col1 = e.parameter.COL1; // current/existing value of col1 - it could be replaced...
var data = e.parameter.DATA.split(','); //new data
var range = sh.getRange((parseInt(index)+1),1,1,data.length);
for (var i = 0; i < rg.length; i++ ) {
if ( index != undefined && i == index && col1 == rg[i][0] ) {
range.setValues([data]);
}
}
return ContentService.createTextOutput("Record updated");
}
// deletes a single record (and its row) from sheet. Requires row index and col1 to match
else if ( fn == 'DELETE' ) {
var index = e.parameter.INDEX; //index in list
var col1 = e.parameter.COL1; // current/existing value of col1 - it could be replaced...
for (var i = 0; i < rg.length; i++ ) {
if ( index != undefined && i == index && col1 == rg[i][0] ) {
sh.deleteRow(parseInt(index)+1);
}
}
return ContentService.createTextOutput("Existing record deleted");
}
// outputs results from SQL query of all data
else if ( fn == 'QUERY' ) {
var rgq = sh.getName() + "!" + sh.getDataRange().getA1Notation();
var sql = e.parameter.SQL;
var qry = '=query(' + rgq + ';\"' + sql + '\";1)';
var ts = ss.insertSheet();
var setQuery = ts.getRange(1,1).setFormula(qry);
var getResult = ts.getDataRange().getValues();
ss.deleteSheet(ts);
return ContentService.createTextOutput(JSON.stringify(getResult));
}
}
this is error:
That is an error in the app....
Try removing JSON.stringify() from the TextOutput part of the script you added.
You need to send data to column I as well....
Unless there is data in columns B,C,D and I it will not work as you expect.
Did you republish your google apps script web app to a new version as well ?
I deleted the data in line 16
Yes
I deleted the data in line 16 and i created new record but not work.
is it possible to create a script that paste the formula into the cell?
And you increment row numbers every time you create a new record
I believe I understand what you want now....
You need array formulas for Columns J & K.
Give me a couple of minutes....
1 Like
OK
Can you please change your CREATE formula in the script back to how it was and republish
In your spreadsheet: (you can make a copy of your data if you want!)
- Delete everything in columns J and K
- Delete all the empty rows in the spreadsheet (this will be from row 16 - row 1000)
- Paste this formula to cell J1
={"Importo_Alex";""; arrayFormula(if(I3:I<>"",I3:I/(B3:B-offset(B3:B,-1,0))*(C3:C-offset(C3:C,-1,0)),""))}
- Paste this formula into cell K2
={"Importo_Iolanda";""; arrayFormula(if(I3:I<>"",I3:I/(B3:B-offset(B3:B,-1,0))*(D3:D-offset(D3:D,-1,0)),""))}
The columns should automatically populate with the correct data
When you add a new row of data, this will be added to the bottom, and the formulas will provide new values
As before you will have to post values to columns B,C,D and I for this to work. You won't get a value in row 2 because there is no value in row 1 to do calculations on.
1 Like