I have a sheet with about 150 scattered cells that I'm trying to convert from formulas to values.
Originally, I set the ranges to an array, and cycled through a setValue(getValue()) in a FOR loop; but as the list of ranges grew, the app hit the point where it started timing out.
I have been able to deduce that a setValues() function is the sensible way to solve the problem, but I can't seem to figure out a way to phrase it properly.
Given an array of:
const rangeArray = ["A1", "B3", "C12"]
What is the correct way to convert the cells in the array from their formulas to their values?
What is the correct way to convert the cells in the array from their formulas to their values?- This question is very opinion-based, but the issue you were having involves a runtime issue. And In order to minimize this, you have to look for other approaches to improve your code such as utilizing other methods or minimizing the usage of other methods in your code.
I have crafted a code which might help you get another perspective on how to deal with such cases by maximizing the usage of getRangeList() and getRanges() in order to avoid using the method getRange() frequently, and loop through each of their values to get and set the data accordingly.
Complete sample code:
function myFunction() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const rangeArray = [
"A1:A3", "B3", "C12", "A5:A8", "B3:B10", "C12:C18", "D1", "D6", "E10:E18", "F3:F7", "G22:G30", "H4", "I12:I16", "J8:J12", "K20", "L5:L9", "M14", "N2:N6", "O18", "P11:P15", "Q7", "R3:R5", "S21", "T9:T14", "U5:U10", "V15", "W1:W4", "X20:X25", "Y7", "Z13:Z18", "A6", "B10:B15", "C3:C7", "D12", "E22:E27", "F9", "G14:G19", "H5", "I11:I15", "J8:J12", "K2", "L17:L22", "M9:M13", "N25", "O4:O8", "P14", "Q6:Q11", "R20:R26", "S3", "T12:T16", "U7:U10", "V18", "W5:W9", "X2:X7", "Y22", "Z10:Z14", "A3:A6", "B8", "C16:C20", "D12", "E5:E9", "F15:F19", "G4", "H10", "I20:I25", "J7:J12", "K3", "L18:L22", "B22", "C4:C9", "D10", "E18", "F3", "G7:G12", "H5:H9", "I15:I19", "J2", "K14:K20", "L10:L13", "M5", "N8:N14", "O12", "P7:P11", "Q16:Q22", "R5", "S9:S15", "T3", "U10:U18", "V14:V19", "W7", "X2", "Y8:Y12", "Z4", "A16:A22", "B11", "C7:C12", "D3", "E8:E14", "F12", "G5:G9", "X9", "Y3", "Z6:Z10","AA1:AA3", "AB3", "AC12", "AA5:AA8", "AB3:AB10", "AC12:AC18", "AD1", "AD6", "AE10:AE18", "AF3:AF7", "AG22:AG30", "AH4", "AI12:AI16", "AJ8:AJ12", "AK20", "AL5:AL9", "AM14", "AN2:AN6", "AO18", "AP11:AP15", "AQ7", "AR3:AR5", "AS21", "AT9:AT14", "AU5:AU10", "AV15", "AJ8:AJ12", "AK2", "AL17:AL22", "AM9:AM13", "AN25", "AO4:AO8", "AP14", "AQ6:AQ11", "AR20:AR26", "AS3", "AT12:AT16", "AU7:AU10"];
const rangesList = sheet.getRangeList(rangeArray).getRanges();
const values = rangesList.map(range => range.getDisplayValues());
rangesList.map((value, valueIndex) => value.setValues(values[valueIndex]));
}
With this code, I have crafted random values with more or less 150 ranges, as a result the code runs successfully with only 1 - 2 seconds duration time. However, please note that you might still encounter delays as you expand your ranges.
Feb 12, 2025, 5:25:41 PM 1.658 s Completed
There are other efficient ways on how to deal with these scenarios, but if this answer does not help you get in the right direction on how to tackle such cases, kindly provide your complete code and a more realistic set of data as the structure of your program and dataset have a lot of factor in terms of runtime issues.
References: