Clear row with spreadsheet formula

[Lukevancleave]

I have a sheet where I use a custom script to get a distance between locations using a call to google maps.  However, I can only make about 50 calls in one day before the script stops working because I'm "using too many that day".  My solution was to make an automation in my App that copies the value from this spreadsheet formula to another column, and then deletes the value from the column with the spreadsheet formula.  That way it's not recalculating the same distances from previous rows every time I load the sheet.  However, when I'm trying to make a Behavior that changes the value of the row (to clear the spreadsheet formula), I'm unable to since it is read only.  Does anyone have any ideas of a work around for this?

[Lukevancleave]

I also tried to make the spreadsheet formula just the Initial Value with a long Concatenate string and that didn't work for two reasons.  One, that it would enter the string with an apostrophe before the = so that it would execute the formula. And two, that part of the formula requires I enter "Miles" with the quotes included and I can't seem to get Concatenate to include the quotes around Miles.  Does anyone have a solution for these too? 

[Antonio_Edvaldo]

Hello,
In my case, it was only possible using a bot for a script call.
Hope to help.

function deleteBlankRows(UrlID) {

const app1=SpreadsheetApp;

// const UrlID = "1nSluamyQXRRAng6_MdbTLBN6YvclpakG7FeduaJxGeQ";

const SS=app1.openById(UrlID);

// Get sheets

var sheets = SS.getSheets();

// Loop through sheets. Delete blank rows in each sheet.

for (var s=0; s < sheets.length; s++) {

var currentSheet = sheets[s];

var sheetName = sheets[s].getName();

var searchDataRange = currentSheet.getRange(1,1,currentSheet.getMaxRows(),currentSheet.getMaxColumns()); // get the ENTIRE sheet. not just where the data is.

var searchValues = searchDataRange.getValues();

var numRows = searchValues.length;

var numCols = searchDataRange.getNumColumns();

var rowsToDel = [];

var delRow = -1;

var prevDelRow = -2;

var rowClear = false;

// Loop through Rows in this sheet

for (var r=0; r < numRows; r++) {

// Loop through columns in this row

for (var c=0; c < numCols; c++) {

if (searchValues[r][c].toString().trim() === "") {

rowClear = true;

} else {

rowClear = false;

break;

}

}

// If row is clear, add it to rowsToDel

if (rowClear) {

if (prevDelRow === r-1) {

rowsToDel[delRow][1] = parseInt(rowsToDel[delRow][1]) + 1;

} else {

rowsToDel.push([[r+1],[1]]);

delRow += 1;

}

prevDelRow = r;

}

}

// Delete blank rows in this sheet, if we have rows to delete.

if (rowsToDel.length>0) {

// We need to make sure we don't delete all rows in the sheet. Sheets must have at least one row.

if (numRows === rowsToDel[0][1]) {

// This means the number of rows in the sheet (numRows) equals the number of rows to be deleted in the first set of rows to delete (rowsToDel[0][1]).

// Delete all but the first row.

if (numRows > 1) {

currentSheet.deleteRows(2,numRows-1);

}

} else {

// Go through each set of rows to delete them.

var rowsToDeleteLen = rowsToDel.length;

for (var rowDel = rowsToDeleteLen-1; rowDel >= 0; rowDel--) {

currentSheet.deleteRows(rowsToDel[rowDel][0],rowsToDel[rowDel][1]);

}

}

}

}

}

[Lukevancleave · Accepted solution]

Someone over on Stackoverflow gave me a great idea.  I'll have it copy over the columns with a behavior, then have another column that is either Unsaved or Saved, and then I'll put an IF( in the formula to run only if it's been "Unsaved"

[JSO]

I have something similar.
The PorteKms column is a real column in my Spreadsheet, of type Number. I also have two columns Origin and Destination (Address) which are the ones that will be used to know the distance.
I have a bot that is activated when the Origin or Destination changes (and that both are not empty) and they are passed as parameters to the Google Script that calculates the distance.
It is only executed when there is a change in source or destination and both contain something. This way so many quotas are not consumed.

[Lukevancleave]

Do you ever encounter a problem when you open the Google sheet? That was my biggest problem. It would be triggered once for AppSheet, but then I might have 50 rows in my Google sheet and when I opened it they would all try and load at once and error out on me. So now I implemented my solution from above and it doesn’t fire if the row has been “saved” even if I just open the sheet 

[JSO]

No.
The Script that I use is not executed from the Spreadsheet and the distance is not calculated in the Sheet (that is why it is not recalculated when loading it).
What I have is a Script file where, among others, there is the function of calculating the distance.
That Script is called from AppSheet using a bot, and is only done when Source or Destination changes and both contain some value.
In short, when you open the spreadsheet the function is not executed, it only does so when any Source or Destination data changes.