getRange() function too slow

30 Views Asked by At
 let idxColuna = {
            0: "A",
            1: "B",
            2: "C",
            3: "D",
            4: "E",
            5: "F",
            6: "G",
            7: "H",
            8: "I",
            9: "J",
            10: "K",
            11: "L",
            12: "M",
            13: "N",
            14: "O",
            15: "P",
            16: "Q",
            17: "R",
            18: "S",
            19: "T",
            20: "U",
            21: "V",
            22: "W",
            23: "X",
            24: "Y",
            25: "Z"
}

let actualRange = idxColuna[numColsInTheRow] + linhaAtual + ":" + idxColuna[(abasEColunas[actualTab].length - 1 + numColsInTheRow)] + linhaAtual
SpreadsheetApp.openById("18Stia0pMXeSv8Vh7UwUCKJYU4ddw0C_8l7csH3lJUJA").getSheetByName("Skiptraced 2.0").getRange(actualRange).setValues([partRow])

This line above is the problem in my app script code.

Explaining "actualRange" and what constitutes it: -In short, actualRange is just a simple variable for me to know which row I will add the knew information in the final column . (actualRange is like an increment) -"idxColuna" is a object to relate indexes to Column letters. -I want to aggregate informations in the same file from different tabs. I'm doing it tab by tab so, for example, if I want 7 columns of information of the first tab, and 5 columns of information from the second tab I have to store it somewhere because when the code will set the final data relate to the second tab in the final spreadsheet it have to insert in the Column relate to idx 7 (G Column) not 0 (A column), because the first 7 columns is already fullfiled with data from the first tab. Basically "numColsInTheRow" is the variable that take care of it. The numColsInTheRow variable is set by a simple iteration and county in the nexts array and object:

//Array with each tab
let abas = ["sheet1", "owner_Emails", "owner_Phone_Numbers", "owner_Mailing_Addresses"]
 
//Object to relate tabs with the columns that I want to get information inside of them
let abasEColunas = {
    sheet1: ["propertyAddress", "zipCode", "city", "county", "state", "name", "units"],
    owner_Emails: ["ownerEmail", "ownerId", "ownerName", "ownerType", "propertyId"],
    owner_Phone_Numbers: ["ownerPhoneNumber"],
    owner_Mailing_Addresses: ["ownerAddress", "OwnerZipCode", "OwnerCity", "OwnerState"]
}

-And Finally "linhaAtual" variable just represents the number of rows in the final spreadtsheet, for us to know which line is the next to complete. Everytime I set a new information, I increment this variable.

So.... I explain all of this, just for you to understand that for the computer calculate the "actualRange" variable is simple and it should be fast.

When I change the "actualRange" variable to some fix Range (ex: "A1:G1") the speed increases absurdly. So I started to think that the problem is because "actualRange" is dynamic. But surprisinly that is not the problem. I test with a simpler incremental Range (just changing the incrementing 1 into the rows for each execution) and it was super fast also. But when I came back to the really "actualRange" variable that I made here, the code was super slow (checking just 50 rows per execution!!!)

Somebody can help me, please??

**If we don't find a solution with only this information I post the full code

0

There are 0 best solutions below