The individual inquiring has a setup with three sheets arranged in columns, featuring multiple rows per data set and various columns for each time period. Instead of replacing the sheets, they wanted a condensed version where filtering could be utilized to focus on relevant data effectively. The objective was to translate each sheet from a columnar format to a row-wise format.
The process itself is relatively straightforward. The original dataset consisted of 64 products with 8 data rows allocated per product. This resulted in an output of approximately 1,350 records.
The main issue faced by the questioner's code arose during the conversion of data to the desired output format. Ensuring that there were exactly 8 rows of data per product was essential, which prompted a validation check within the code to confirm this requirement. Additionally, specific sheets were identified using names (via getSheetByName) to facilitate easy application of the code to any designated Input Sheet and Output sheet. However, it was imperative that both sheets existed prior to running the code.
An initial fix was made to address the coding hiccup encountered by the questioner. By implementing the methodology of getDataRange and getValues before the looping process, performance greatly improved. Two loops were employed - one vertical for moving through the data rows, and the other horizontal for navigating the time-related columns. While the original implementation suffered from inefficiency leading to timeouts, a modification involving building a single 2D array and only saving it to the Output sheet once at the end significantly boosted performance. Completion time dropped drastically from several minutes to under 5 seconds.
function so5243560403() {
// The function for creating a clean Output Sheet
}
function isInt1(value) {
return !isNaN(value) && parseInt(Number(value)) == value && !isNaN(parseInt(value, 10));
}
UPDATE
A critical component in the questioner's code pertained to updating data on an "Output Sheet" when modifications were made to "Category” sheets. Although the base structure of the update code was sound, a pivotal element missing was translating the source range on the Category Sheet to establish the corresponding target range on the Output sheet.
The solution revolved around employing a mathematical numeric sequence approach. Specifically, the numerical sequence represented the row numbers for products on the source sheet, with each product spanning 8 rows starting from the first row (#10). Hence, given the row number of a change event, determining the relevant product group required computations based on the sequential pattern. As changes often occurred across different rows within a product, calculating the precise product grouping and subsequently identifying the altered fields were crucial steps efficiently implemented via algorithmic formulas.
The functionality incorporated within the updated section systematically decoded the relationships between changed cells, product sequences, week numbers, and associated field alterations on the Output sheet. By leveraging nuances like row differences, cycle positions, and column shifts, the updated code successfully pinpointed the exact locations for introducing new values obtained post-edit events.
function OnEdit(e) {
// Functionality for updating relevant Outputsheets based on edits in Category sheets
}