Currently, I am addressing a challenge within my organization that requires me to maintain some level of ambiguity. However, I have been granted permission to discuss this issue openly.
The task at hand involves creating a script to analyze a Google Sheet containing a column of numerical data. The objective is for the script to identify and return a series of ranges (specified in A1 notation) where the sum of the numerical values approximates 0.25 without exceeding it. This process should encompass all relevant ranges on the sheet. For example, given the following dataset:
A (COLUMN HEADER)
DataHeader (A1)
.1 (A2)
.01 (A3)
.04 (A4)
.1 (A5)
.03 (A6)
.02 (A7)
.1 (A8)
.05 (A9)
.04 (A10)
.07 (A11)
The expected output from the script would be [A2:A4, A5:A10, A11], since A2:A4 sums up to exactly 0.25, while A5:A10 falls just below 0.25 with the inclusion of A11 tipping the total above 0.25. Including A11 is necessary for retaining the complete dataset.
In practice, the script generates larger ranges specified in A1 notation. The main challenge I am encountering lies in constructing an accurate running total and extraction of the relevant rows.
As of now, my script comprises two functions to address separate aspects of the problem:
The initial function identifies the essential information – the dataset for analysis and the number of rows from the top of the sheet where the data begins
var data
and
var A1offset
The second function focuses on calculating the cumulative sum of the dataset array and compiling pairs of row numbers that represent ranges where the total remains slightly under 0.25. Below is the snippet causing the error:
var result = [] //empty array to store row number pairs.
//data is a previously obtained array of my data of interest
//A1offset is the number of rows between the top of the sheet and the start of data
for (var i = 0;i<data.length;i++){
cursum += data[i][0]//add current value to cursum
if( cursum >= .25 && result.length<1){//if we are @ the beginning add first rownum (A1 Notation) of data to result
i=i-1 //Go back one so that i++ runs, we don't miss this datapoint.
cursum = 0 //reset
result.push([(A1offset),(i + A1offset )])//
}
else if( cursum >= .25 && result.length>=1){
i=i-1//go back one
cursum = 0//reset
result.push([(result[result.length-1][1]+1),(i+A1offset)]) //Add to result a pair of values, the
//row after the end of the last range and the current row.
}
}The roadblock I'm facing emerged post the addition of the i=i-1 statement. It triggers an 'Out of Memory' error when executed. Interestingly, the issue does not arise when working with a smaller dataset. Experimentally, limiting iterations to .85*data.length resolved the matter. The dataset in question contains 4978 elements.
I'm left pondering if there exists a predefined cap on the number of iterations permissible in Google scripts. Considering my limited background in computer science, any insights or suggestions on improving the code efficiency would be greatly appreciated. Please feel free to ask for additional details if needed.
You can explore a sample version of my sheet along with the full script provided below (highly commented for clarity):
function IDDATA() {
//ID the data to sum
var spsheet = SpreadsheetApp.getActive(); //Get spreadsheet
var tab = spsheet.getSheetByName("Test") //Fetch data sheet
var Drows = tab.getRange("A:A").getValues() //Capture all Column D values
var filledvals = Drows.filter(String).length //Identify filled rows count in Column D
var offset = 0 //Initialize offset as 0
var ct1 = 0
while(Drows[ct1][0].indexOf("DATA")<0){
ct1++
}
offset = ct1
var A1offset = ct1 + 2
var datarows = filledvals-2
return([datarows,offset,A1offset])
}
function RUNSUM(){
var firstcol = "A"
var lastcol = "A"
var spsheet = SpreadsheetApp.getActive();
var tab = spsheet.getSheetByName("Test")
var vararray = IDDATA()
var len = vararray[0]
var offset = vararray[1]
var A1offset = vararray[2]
var startrow = "A"+A1offset
var endrow = "A"+(len+A1offset)
var cursum = 0
var range = tab.getRange(startrow+":"+endrow)
var data = range.getValues()
var testmax = Math.floor(.85*data.length)
var result = []
for (var i = 0;i<.8*data.length;i++){
// Data processing logic here
}
var rangearray = []
var intarray = []
for(var k = 0; k < result.length; k++){
intarray.push([result[k][0].toFixed(0),result[k][1].toFixed(0)])
}
for (var j = 0;j<result.length;j++){
rangearray.push(firstcol+intarray[j][0]+":"+lastcol+intarray[j][1])
}
Logger.log(rangearray)
return rangearray
}