Is there a better command to move cell values & properties to another area?

[Stefan Cook]

I have a working script that gets a column of cells (30,000 rows), getting the value, Font Color, Background, and Font Weight for each.  It checks for "non-normal" Font Colors, Backgrounds, and/or Font Weights.  If any are found it copies all values and properties to their respective arrays.  Once all 30,000 rows have been processed, it write the array to an area after the 30,000.  One Read and one Write.

Is there a single command that will basically get the cells' value and associated properties and then another command to write the cells' value and associated properties?  I am thinking along the lines of something like the "copyto" method but I need to first interrogate the data, grab what I need, and write that (a much smaller subset read in).

The below code is what I am using, but as you can see, there are 4 Reads then 4 Writes.  I am looking to improve my performance because the conversion of the getFontColorObjects' Color[][] object to RGB string 2D array takes over 30 second alone.  I use that command since getFonColors is now deprecated.

var inpRange = sheet.getRange(1,2,30000);

var inpValues = inpRange.getValues();
var inpFontColorsObj = inpRange.getFontColorObjects();
var inpBckgnds = inpRange.getBackgrounds();
var inpFontWeights = inpRange.getFontWeights();

outRange.setValues(outValuesArray);
outRange.setFontColors(outFontColorsArray);
outRange.setBackgrounds(outBckgndsArray);
outRange.setFontWeights(outFontWeightsArray);

[Keith Andersen]

Instead of collecting all the values and then looping through each cell ...have you considered an arrayformula?

Can you share your sheet?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/85c8f8b2-89d8-4cbe-ac4e-f68e9bbcf523n%40googlegroups.com.

[Stefan Cook]

I don't know if arrayformula would work or not.  I would have to research it further to see if it would feasible.

I don't think my company would allow that so I took a small snippet of the column with the possible values that I would encounter.  Farther down is the output that should be seen.

[INIControl]

File = OPT, FileNames, File0

Menu = MainMenu

Validate = InputValidation

Link = LinkedINIandOptionFiles

RestoreMenus = True

[FileNames]

N/A

N/A

N/A

TerminalReadFile = <$AMO:>

Allow99MemberDoOnlineOrders = 45, BOOLEAN, 1

GroupReadFile = <$AMOG:>

ExpandedBIN = 213, RECORD

hppFile = incoptns.hpp

DrawerWarningLimit = 5, ULONG, 120000 [or 200000 originally in Prod]

terminalIndicator = **%TRM**

CommEncryption = 18, BOOLEAN, 0

saveINIFile = eamoptns.ini

TerminalSalesType = 61, UINT, 3

authorizations = <ACEAUTHZ>

Should output the following

[FileNames]

N/A

N/A

N/A

Allow99MemberDoOnlineOrders = 45, BOOLEAN, 1

ExpandedBIN = 213, RECORD

CommEncryption = 18, BOOLEAN, 0

TerminalSalesType = 61, UINT, 3

**note that the light blue font is not present in the output because that denotes something not needed.

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/C1XNv5e5udA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4xXAKBVAWbKpBw-LAZJ16ZB%2BO26TsGSwp%3D00cF05Nqv1g%40mail.gmail.com.

--

Stefan Cook
IS WAREHOUSE TEST AUTO ANALYST

(POS / RTM Automation)

Costco Wholesale Corp
POS DEV - Bldg 6
21930 SE 51st St
Issaquah, WA 98029
425.416.2703 (Office)
Stefa...@Costco.com (Email)

[Stefan Cook]

In thinking about it I doubt the arrayformula would work as I have to add this caveat.

I also want to keep the label along with the colored item.  So the following would result in the output further down.

[INIControl]

File = OPT, FileNames, File0

Link = LinkedINIandOptionFiles

Link = LinkedINIandOptionFiles

Link = LinkedINIandOptionFiles

output

[INIControl]

Link = LinkedINIandOptionFiles

[Keith Andersen]

I assume that you need to filter OUT any entry where the cell background, cell text isn't a single standard like :

cell background = white

font weight = normal

font color = black

font style = Times Roman

or....

is there a range of acceptable colors, fonts, styles and weights?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAF7yU5nXEZmL6UZReOJ81FfSwOYtzuXSRGG15dA9cDaiyYpyGw%40mail.gmail.com.

[Stefan Cook]

Yes, basically filter out with the exception of remaining cells (colored cells) needing the [label] that the coloration happened in and that would be normally "normal", and not every label will have a coloration within it (see my previous email).

"normal"   (*with the caveat of [label] mentioned above)

cell background = white

font weight = normal

font color = black

(don't care about style)

 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4zbdTcWSaQAarmPxri61Vdmi%3DoiJWvQd%3DcyzbAy50FvbQ%40mail.gmail.com.

[Keith Andersen]

How many labels are there?

Are labels always enclosed in brackets [ ]?

Are there only two columns that you need checked?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAF7yU5nirsUkWiHWRXCNA%2B_Mxk1Uv_uKkYbUXfsZ7u-BCVW8sw%40mail.gmail.com.

[Stefan Cook]

How many labels are there?

There is around 100-200 labels within the 30,000 rows (from the output there would probably be only around 100 rows to write).

Are labels always enclosed in brackets [ ]?

Yes (some labels will be red font, some gray with lite orange background, but majority will be black font/white background)

Are there only two columns that you need checked?

I check one column at a time (meaning do process on one column, then redo the process again on a different column, repeat 4 more times for other columns) 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4zLmUoeBnLyDgdga9ur-fGR1-zNBSvD1ooUt8ritg84bg%40mail.gmail.com.

[Keith Andersen]

Could you please list your column headers?

Could you show all of your process script?

On Thu, May 25, 2023 at 9:57 PM Keith Andersen <keith.a...@gmail.com> wrote:

Could you please list your column headers?

Could you show all of your process script?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ec9733a3-173e-42f1-a545-ff0a3c7eb25en%40googlegroups.com.

[Keith Andersen]

Could you list your column headers?

Could you also post your whole script?

Without seeing your sheet and it's setup, it's difficult to formulate a process.

For instance. Red and blue text should be retained but light blue text and black text should not... unless the black text is a label.

White background is ok if text is not black or it it is a label.

Aside from color of background or color / weight of text - are there other constants that could be used for filtering? For instance - every label is bracketed [ ].

Seeing your script might help me visualize your method and formulate a different/ faster approach.

Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAF7yU5mCRSET9YL%2B8GDUC1QEtLDvSetVu2XVZujAj-PHiy2d7g%40mail.gmail.com.

[Stefan Cook]

A little background on the spreadsheet.  The column contains the option file of a particular code drop and so the column has new labels and content added while there are other labels, and content, that get removed or name modified.  Then there are additional lines added to the end of a label.  So red font means a change and lite orange background is basically a removed option.  Light blue font is a constant.

Here is the first 25 lines of a column.

EAMOPTNS

A136.01CD (Default) -OS TCxSky 0103

//****************************************************************************

// PERSONALIZATION PARAMETER VALUES

//

// FROM: <$AMOPTNS>

// DATE: 04/05/2023 14:46

//****************************************************************************

[INIControl]

File = OPT, FileNames, File0

Menu = MainMenu

Validate = InputValidation

Link = LinkedINIandOptionFiles

RestoreMenus = True

[FileNames]

OptionsFile = eamoptns

StoreReadFile = <$AMOPTNS>

And here is the whole script.  If you have pointers on how to do something else better then I am all ears too.

function differencesSummary(inCol)

{

  var dummyUi = SpreadsheetApp.getUi();               // for debugging, just a dummy UI for Alert usage

  //                                                  // SSF = Call to Server-side Apps Script Function (read-write to Google Server)

  var ssa = SpreadsheetApp.getActiveSpreadsheet();    // SSF, gets Active Spreadsheet           (returns Spreadsheet object)

  var sheet = ssa.getActiveSheet();                   // SSF, gets Active Sheet                 (returns Sheet object)

  var sheetLastRow = sheet.getLastRow();              // SSF, get last row of data in sheet (of longest column)   (returns Integer)

  var maxRow = sheet.getMaxRows();                    // SSF, get last available row in sheet   (returns integer)

  var wholeColumn = sheet.getRange(1, inCol, sheetLastRow); // SSF, referencing whole column range to worked from  (returns Range object),14=skip hdr

  var data = wholeColumn.getValues();                       // SSF, reads in all values in range      (returns 2D array)

  var term = 'Script Range1';                         // search term for finding start of analysis area

  // array used, return val 1 less on spreadsheet

  var endOptRow = data.findIndex( parm1 => {          // Prototype function

                    return parm1[0] == term });       // array(data) finds 1st element, parm1 is parm which is element of array as temp array

  if (endOptRow == -1) {                                                // if no text was found

    dummyUi.alert("Must put 'Script Range1' at start of Option Diffs"); // alert dialog with error message

    return;                                                             // exit?

  }

 

  var inpOptRange = sheet.getRange(1,inCol,endOptRow); // SSF, referencing options range to worked from  (returns Range object)

  var inpOptValues = inpOptRange.getValues();         // SSF, get text of options in range of cells  (returns 2D array)

  var inpOptFontColorsObj = inpOptRange.getFontColorObjects(); // SSF, get Font Color of options in range of cells  (returns Color[][] object)

  var inpOptBckgnds = inpOptRange.getBackgrounds();            // SSF, get Background color of opts in range of cells  (returns 2D array)

  var inpOptFontWeights = inpOptRange.getFontWeights();        // SSF, get Font bold of opts in range of cells (returns 2D array)

  var ndx = -1;                                       // set index to "not used yet", used for first time usage in certain circumstances

  var regex = /^\[.+\]$/;                             // used for searching of the [xxxx] labels

  var isLabel = false;                                // set as not a label

  var lastCopiedLabel = "Bogus_label";                // set last written label (bogus txt needed for 1st compare, especially if no label & colored)

  var currentLabelName = "";                          // will hold current label's name (will be used to write if any opt changes)

  var currentLabelFontColor = "";                     // will hold current label's Font Color so it will overlay output line if label used

  var currentLabelBckgnd = "";                        // will hold current label's Background so it will overlay output line if label used

  var currentLabelFontWeight = "";                    // will hold current label's Font Weight so it will overlay output line if label used

  var outOptValuesArray = [];                         // empty array for values to be written  (to be 2D array)

  var outOptFontColorsArray = [];                     // empty array for Font Color values to be written (to be 2D array)

  var outOptBckgndsArray = [];                        // empty array for Background Color values to be written  (to be 2D array)

  var outOptFontWeightsArray = [];                    // empty array for Font Weight values to be written  (to be 2D array)

  var inpOptFontColors = [];                          // empty array for input of Font Colors (due to Color objs needing conversion to string RGB vals)

  for (var i in inpOptFontColorsObj) {                // loop through all elements of Color[][] object array, to convert from obj to 2D array

    inpOptFontColors.push([inpOptFontColorsObj[i][0]

                           .asRgbColor()

                           .asHexString()]);          // append, put RGB string for Font Color into new inp array (as an array iteself)

  }

  const arrLen = inpOptFontColors.length;             // set to length of Font Color array elements so not re-issueing length in loop

  for (var i=14; i<arrLen; i++) {                     // go thru each element (eg. column cells in SS), starts after heading info in SS (line 14)

    if (regex.test(inpOptValues[i][0])) {             // if is [xxx] label

      currentLabelName = inpOptValues[i][0];          // save the label name as the current label

      currentLabelFontColor = inpOptFontColors[i][0]; // save label's Font Color as the current label Font Color

      currentLabelBckgnd = inpOptBckgnds[i][0];       // save label's Background as the current label Background

      currentLabelFontWeight = inpOptFontWeights[i][0]; // save label's Font Weight as the current label Font Weight

      isLabel = true;                                 // element reviewed IS a label

    } else {                                          // element not [xxx] label

      isLabel = false;                                // element reviewed is not a label

    }

    // If font not black & not lite blue (manually changed options color) & not empty or not white background.

    //   See Programming/Formula Information Help tab for extra info on "#ff000000" vs "000000"

    if (inpOptFontColors[i][0] !== "#ff000000" &&

        inpOptFontColors[i][0] !== "#6d9eeb" &&

        inpOptValues[i][0] != ""         ||

        inpOptBckgnds[i][0] !== "#ffffff") {          // Value & Type compare (strict equality)

      ndx++;                                          // increment for later use to know number of times thru areas

      if (currentLabelName == lastCopiedLabel) {      // if element to be copied is under same label as last copied label

        outOptValuesArray.push([inpOptValues[i][0]]);           // append text value to output values array

        outOptFontColorsArray.push([inpOptFontColors[i][0]]);   // append Font Color of text to output Font Colors array

        outOptBckgndsArray.push([inpOptBckgnds[i][0]]);         // append Background of text to output Backgrounds array

        outOptFontWeightsArray.push([inpOptFontWeights[i][0]]); // append Font Weight of text to output Font Weights array

      } else {                                        // else -labels are different

        if (isLabel) {                                // if is a new label, which font is non-black

          if (ndx > 0) {                              // if not 1st time thru

            outOptValuesArray.push([""]);             // append empty value to output values array

            outOptFontColorsArray.push(["#ff000000"]); // append black Font Color to output Font Colors array

            outOptBckgndsArray.push(["#ffffff"]);     // append white Background to output Backgrounds array

            outOptFontWeightsArray.push(["normal"]);  // append non-bold Font Weight to output Font Weights array

            //ndx++;                                    // increment for next output arrays element index (after blank line)

          }

          outOptValuesArray.push([currentLabelName]);             // append text value to output values array

          outOptFontColorsArray.push([inpOptFontColors[i][0]]);   // append Font Color of text to output Font Colors array

          outOptBckgndsArray.push([inpOptBckgnds[i][0]]);         // append Background of text to output Backgrounds array

          outOptFontWeightsArray.push([inpOptFontWeights[i][0]]); // append Font Weight of text to output Font Weights array

        } else {                                      // isn't label but is 1st "new" item for diff label

          if ((ndx > 0) ||

              (ndx == 0 && currentLabelName != "")) { // if not 1st time thru or if 1st time here & actually has a label name prev

            if (ndx > 0) {                            // if not 1st time thru

              outOptValuesArray.push([""]);           // append empty value to output values array

              outOptFontColorsArray.push(["#ff000000"]);  // append black Font Color to output Font Colors array

              outOptBckgndsArray.push(["#ffffff"]);       // append white Background to output Backgrounds array

              outOptFontWeightsArray.push(["normal"]);    // append non-bold Font Weight to output Font Weights array

              //ndx++;                                  // increment for next output arrays element index (after blank line)

            }  

            outOptValuesArray.push([currentLabelName]);            // append text label (colored item element belongs to) into out val arr

            outOptFontColorsArray.push([currentLabelFontColor]);   // append label's Font Color to output Font Colors array

            outOptBckgndsArray.push([currentLabelBckgnd]);         // append label's Background to output Backgrounds array

            outOptFontWeightsArray.push([currentLabelFontWeight]); // append label's Font Weight to output Font Weights array

          } else {                                    // 1st time through AND no label so set own "bogus" label info

            currentLabelName = "[No Label]";          // set bogus label as current since no label first, for use if more non-label cells

            outOptValuesArray.push(["[No Label]"]);     // use bogus text label since no label first, copy into out val arr

            outOptFontColorsArray.push(["#999999"]);    // use gray Font Color and copy into output Font Colors array

            outOptBckgndsArray.push(["#fce5cd"]);       // use lite orange Background and copy into output Backgrounds array

            outOptFontWeightsArray.push(["bold"]);      // use Bold Font Weight and copy into output Font Weights array

          }

          ndx++;                                      // increment for next output arrays element index

          outOptValuesArray.push([inpOptValues[i][0]]);           // append text value (of colored element) to output values array

          outOptFontColorsArray.push([inpOptFontColors[i][0]]);   // append Font Color to output Font Colors array

          outOptBckgndsArray.push([inpOptBckgnds[i][0]]);         // append Background to output Backgrounds array

          outOptFontWeightsArray.push([inpOptFontWeights[i][0]]); // append Font Weight to output Font Weights array

        }

        lastCopiedLabel = currentLabelName;           // set as new last copied label

      }

    }

  }

  term = 'Script Range2';                             // search term for finding start of Changes analysis area

  // array used, return val 1 less on spreadsheet     // syntax - array.findIndex(function(currentValue, index))

  var startWriteRow = data.findIndex( parm1 => {      // Prototype function, array(data) finds 1st element, parm1 is parameter

                        return parm1[0] == term },

                         endOptRow);                  //  parm1 is element of array as temp array, start from Range1 index instead of start of array

  if (startWriteRow == -1) {                                           // if no text was found

    dummyUi.alert("Must put 'Script Range2' at start of New Changes"); // alert dialog with error message

    return;                                                            // exit?

  }

 

  startWriteRow = startWriteRow + 7;                  // need +7 to get out of output header area, for output

  var startWrtNumRows = outOptValuesArray.length;     // set len same as size of output values array, setValues+ req same size as out 2d array

  var outOptRange = sheet.getRange(startWriteRow,

                                  inCol,

                                  startWrtNumRows);   // SSF, output writeable area to put all data from arrays  (returns Range object)

  outOptRange.setValues(outOptValuesArray);           // SSF, put ALL values in output values array into empty section in range

  outOptRange.setFontColors(outOptFontColorsArray);   // SSF, put ALL Font Colors assoc with values above into empty section in range

  outOptRange.setBackgrounds(outOptBckgndsArray);     // SSF, put ALL Background Colors assoc with values above into empty section in range

  outOptRange.setFontWeights(outOptFontWeightsArray); // SSF, put ALL Font Weights assoc with values above into empty section in range

         

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4yJdV9ALJXCC6maUe59d3xf3d4jNrhe1qu4b0xopdRHfw%40mail.gmail.com.

[Stefan Cook]

Additional info on the script.  I search for the words 'Script Range1' and 'Script Range2'.  These denote "end"/"beginning" areas.

'Script Range1' is the end of the option rows (the input or read area), so from row 1 to about row 30,000 is the input area.

'Script Range2' is the start of the coloration differences found area (ie. the output or write area), so from row 30,000+x to the end of the spreadsheet is the output area.

I have an area between Range1 and Range2 that I do other stuff.

How they are shown in the spreadsheet

Script Range1

Script Range2

[Jose armando Jara Osores]

To get the value of a cell, you can use the `getCellValue()` command and specify the cell's coordinates. For example:

```

var cellValue = getCellValue(2,3);

```

This code would get the value of the cell in row 2 and column 3.

To write the value of a cell, you can use the `setCellValue()` command and specify both the cell's coordinates and the value you want to write. For example:

```

setCellValue(2,3,"new value");

```

This code would write "new value" to the cell in row 2 and column 3.

It is also possible to get and write cell properties using the `getCellProperties()` and `setCellProperties()` commands respectively. To get and write properties to an entire row or column, use the `getRowProperties()` and `setRowProperties()` and `getColumnProperties()` and `setColumnProperties()` commands, respectively.

I hope this information is useful to you.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAF7yU5mc3SQ0DuEQOdwTnWHTq%3DOooo3cjfMBBbqfgFjGSs4ayw%40mail.gmail.com.

[Keith Andersen]

Stefan,

I've tried a few things on my end testing a couple of concepts that did not pan out. I think you're code is just fine, excellent actually, and I don't think I could come up with a way to speed it up more than what's working for you right now.

I learned a few things tinkering around and I thank you for the opportunity to look at your query.

Keith

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAOPpvwoUeOvt-Zk9muswMXJmWmE7P_3B9qHpcQ1kUxxSON2Rzg%40mail.gmail.com.