Coping automatically the value of a cell adding some rows

[Balubeto Balubeto]

Hi

I would like that every time I add one or more rows in areas where column A contains the cell with the “Date e ricevute” string, the added rows automatically have the aforementioned string in their column A cells.

I have created this sample spreadsheet:

https://docs.google.com/spreadsheets/d/1o7Jq2419Vglp6gmBccK17gwt3gq2LsKEpYZT7m3HVQ0/edit?usp=drive_link

Thanks

Bye

[Brent Guttmann]

const C_GRUPO = '#e0f2f1', C_SUB = '#f5f5f5', C_TOTAL = '#ffffff',

      C_DATA = '#fff9c4', C_PERS = '#ffe0b2',

      TXT_MAG = '#ff00ff', TXT_GRAY = '#999999', TXT_BLK = '#000000';

function onOpen() {

  SpreadsheetApp.getUi()

    .createMenu('🎨 Formattazione colori categorie spesa')

    .addItem('Applica colori categorie', 'applyFmt')

    .addToUi();

}

function onEdit(e) {

  const s = e.range.getSheet(), r = e.range;

  if (r.getColumn() !== 1) return;

  const row = r.getRow(),

        val = r.getValue(),

        above = row > 1 ? s.getRange(row - 1, 1).getValue() : '';

  if (!val && above === 'Date e ricevute') {

    s.getRange(row, 1).setValue('Date e ricevute');

  }

  if (s.getLastRow() >= 7) applyFmt();

}

function onChange(e) {

  if (e.changeType !== 'INSERT_ROW') return;

  const sh = e.source.getActiveSheet();

  const rows = sh.getActiveRangeList()?.getRanges()?.[0];

  if (!rows) return;

  const start = rows.getRow();

  const count = rows.getNumRows();

  const above = start > 1 ? sh.getRange(start - 1, 1).getValue() : '';

  if (above === 'Date e ricevute') {

    const vals = Array.from({length: count}, () => ['Date e ricevute']);

    sh.getRange(start, 1, count, 1).setValues(vals);

  }

  applyFmt();

}

function applyFmt() {

  const sh = SpreadsheetApp.getActiveSheet();

  const last = sh.getLastRow();

  if (last < 7) return;

  const rng = sh.getRange('A7:Z' + last),

        fc = rng.getFontColors(),

        nf = rng.getNumberFormats(),

        bg = rng.getBackgrounds();

  for (let i = 0; i < fc.length; i++) {

    let clr = null;

    switch (fc[i][0]) {

      case TXT_MAG: clr = C_GRUPO; break;

      case TXT_GRAY: clr = C_SUB; break;

      case TXT_BLK:

        if (rng.getCell(i + 1, 1).getValue()) clr = C_TOTAL;

        break;

    }

    if (clr) bg[i].fill(clr);

  }

  for (let i = 0; i < nf.length; i++) {

    for (let j = 0; j < nf[i].length; j++) {

      const f = nf[i][j];

      if (/[dmy]/.test(f)) bg[i][j] = C_DATA;

      else if (f === '# ???') bg[i][j] = C_PERS;

    }

  }

  rng.setBackgrounds(bg);

}

You will need to add an onChange trigger in the triggers page of the sheet's scripts page.

• Function to run: onChange
• Source: Spreadsheet
• Type: On Change

[Balubeto Balubeto]

I tried it, but it doesn't work because I inserted the rows 10, 11, and 14 as example, but the “Date e ricevute” string  does not appear in cells A10, A11, and A14.

In addition, the color of cells B10:Y10, B11:Y11, and B14:Y14 are not the same color as the rows above them.

How come?

Thanks

Bye

[Brent Guttmann]

Did you set up the trigger?

[Balubeto Balubeto]

I made the change but nothing has changed, i.e., the cells in column A of the inserted rows remain empty.

Why is that?

Thanks

Bye

[Brent Guttmann]

As I said, you need to add the trigger.

https://script.google.com/u/0/home/projects/1sS1W7ZySJlMYvnk4Nv8G8pJd0bUHszUuT9Nb6OUollb-GiM2N5dGNSgT/triggers

--
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/J2PHg-Ha7p8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/a17327ca-cb35-4ac4-af0e-ac6a0785b0fan%40googlegroups.com.

[Balubeto Balubeto]

Now the script works, and I've learned something new thanks to you.

To conclude, in the rows where the cells in column A contain the "Date e ricevute” string is it possible to ensure that the related Date cells (i.e., those in columns B, D, F, H, J, L, N, P, R, T, V, and X) are yellow with blue text and those of the Custom Currency type (i.e., those in columns C, E, G, I, K, M, O, Q, S, U, W, and Y) are blue with yellow text?

Thanks

Bye

[Balubeto Balubeto]

Could you finish your script, as I really need it (see above)?

Thanks

Bye

[Brent Guttmann]

Sorry, I do not have time for this. I suggest you use conditional formatting. I am sure chatgpt can help you out. Good luck!

On Thu, Oct 30, 2025 at 12:24 PM Balubeto Balubeto <balu...@gmail.com> wrote:

Could you finish your script, as I really need it (see above)?

Thanks

Bye

Il giorno lunedì 27 ottobre 2025 alle 10:39:20 UTC+1 Balubeto Balubeto ha scritto:

Now the script works, and I've learned something new thanks to you.

To conclude, in the rows where the cells in column A contain the "Date e ricevute” string is it possible to ensure that the related Date cells (i.e., those in columns B, D, F, H, J, L, N, P, R, T, V, and X) are yellow with blue text and those of the Custom Currency type (i.e., those in columns C, E, G, I, K, M, O, Q, S, U, W, and Y) are blue with yellow text?

Thanks

Bye

Il giorno domenica 26 ottobre 2025 alle 16:09:43 UTC+1 Brent Guttmann ha scritto:

As I said, you need to add the trigger.

https://script.google.com/u/0/home/projects/1sS1W7ZySJlMYvnk4Nv8G8pJd0bUHszUuT9Nb6OUollb-GiM2N5dGNSgT/triggers

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/fc755953-9188-4c22-a81e-03f4d1834e25n%40googlegroups.com.

[Balubeto Balubeto]

I understand.

When you have some time, remember to finish this script, as I think it will be very useful in several projects.

Thanks

Bye

[Balubeto Balubeto]

Sorry again, but I noticed a bug in your script:

When I add a row, the blank rows 42, 47, and 54 are automatically colored.  How come?

Thanks

Bye  

[Brent Guttmann]

What I added only applies the same coloring to the row above the one that was inserted. I integrated it with the code you originally had, so you might want to check that section or your conditional formatting to identify the cause.

I recommend taking some time to review the code, understand how it works, and debug it yourself, as I don’t plan to spend further time on this. It might be a language barrier issue, but I also suggest being mindful of how you ask for help on this forum; at times, your requests can come across as if others owe you a solution.

On Fri, Oct 31, 2025 at 7:31 AM Balubeto Balubeto <balu...@gmail.com> wrote:

Sorry again, but I noticed a bug in your script:

When I add a row, the blank rows 42, 47, and 54 are automatically colored.  How come?

Thanks

Bye  

Il giorno venerdì 31 ottobre 2025 alle 09:09:24 UTC+1 Balubeto Balubeto ha scritto:

I understand.

When you have some time, remember to finish this script, as I think it will be very useful in several projects.

Thanks

Bye

Il giorno giovedì 30 ottobre 2025 alle 17:33:59 UTC+1 Brent Guttmann ha scritto:

Sorry, I do not have time for this. I suggest you use conditional formatting. I am sure chatgpt can help you out. Good luck!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/1dc3f0a8-b7b9-46f9-ae6a-3f1b55f2c391n%40googlegroups.com.

[Balubeto Balubeto]

What a nice problem, since I don't know anything about programming.

Please, could you fix that bug?

Thanks

Bye

[Balubeto Balubeto]

This morning, I opened my spreadsheet and neither the Extensions menu nor your script menu were there.
Now, only the first menu has reappeared.

What happened?

Thanks

Bye

[Brent Guttmann]

I completed and tested your code fix on October 30th, despite saying I didn’t have time. The change is noted in the comment at the top of the script. You’re welcome. I’ve spent all the time I intend to on this and won’t be making additional updates.

Take care,

Brent

On Mon, Nov 3, 2025 at 11:45 AM Balubeto Balubeto <balu...@gmail.com> wrote:

This morning, I opened my spreadsheet and neither the Extensions menu nor your script menu were there.
Now, only the first menu has reappeared.

What happened?

Thanks

Bye

Il giorno sabato 1 novembre 2025 alle 09:26:22 UTC+1 Balubeto Balubeto ha scritto:

What a nice problem, since I don't know anything about programming.

Please, could you fix that bug?

Thanks

Bye

Il giorno venerdì 31 ottobre 2025 alle 14:12:59 UTC+1 Brent Guttmann ha scritto:

What I added only applies the same coloring to the row above the one that was inserted. I integrated it with the code you originally had, so you might want to check that section or your conditional formatting to identify the cause.

I recommend taking some time to review the code, understand how it works, and debug it yourself, as I don’t plan to spend further time on this. It might be a language barrier issue, but I also suggest being mindful of how you ask for help on this forum; at times, your requests can come across as if others owe you a solution.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/0985e9e7-21f6-4bbc-b7b2-d2b31829a381n%40googlegroups.com.

[Balubeto Balubeto]

Finally, I have restored my spreadsheet with your script working.

The thing I don't understand is why the cells of rows 70 and 71 and those of D131, D132, D133, and D134 are colored in yellow, since your script should ignore the aforementioned cells.

Thanks

Bye

[Keith Andersen]

Understandably, Brent has limited time to spend on this project. If you'll share It with me. I will try and help work out some of the bugs you're finding.

Keith 

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

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 visit https://groups.google.com/d/msgid/google-apps-script-community/575ceeb0-b129-4bd3-938d-016a866979f6n%40googlegroups.com.

[Balubeto Balubeto]

The link to my spreadsheet is in the first message.

Thanks

Bye

[Keith Andersen]

Could you redo it because it's not working for me. Please repost here.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/e6450072-ffe5-4eba-8de6-be00fc33d246n%40googlegroups.com.

[Balubeto Balubeto]

The link is: https://docs.google.com/spreadsheets/d/1o7Jq2419Vglp6gmBccK17gwt3gq2LsKEpYZT7m3HVQ0/edit?usp=sharing .

Thanks

Bye

[Balubeto Balubeto]

Now, when I run the script, I get the following errors:



How come?

Thanks

Bye

[Balubeto Balubeto]

How come I get the above error?
How can I fix it?

Thanks

Bye

[Balubeto Balubeto]

I noticed that when I run the script from the menu, the execution function is ApplyFmt rather than OnChange, and I get the error “ReferenceError: v is not defined.” How come?

Thanks

Bye

[Keith Andersen]

At this point - please explain exactly what you want/need the script to do. Please be as specific as possible.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/eb107f84-da86-4e8a-9b24-7b4d34e78f0cn%40googlegroups.com.

[Balubeto Balubeto]

When I run the script from Apps Script, I get the error " TypeError: Cannot read properties of undefined (reading ‘changeType’)":

When I run it from the spreadsheet menu, I get the error “ReferenceError: v is not defined”:

Why are these errors displayed?

How can I fix these errors so that this script no longer displays them?

Thanks

Bye

[Keith Andersen]

Again...

At this point - please explain exactly what you want/need the script to do. Please be as specific as possible about your process needs / expectations. Knowing this then I can look at the script to see what it's supposed to do.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/2bc57d40-782f-40d4-acff-8dfab02b75e9n%40googlegroups.com.

[Brent Guttmann]

You cannot run onChange from the appscript editor, it expects an event (e) to be passed -- onChange(e) 

--

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BGC-qB%2Bd_Y4c8eapmJhoonrdj8jo1dorO9WWBXMre729g%40mail.gmail.com.

[Balubeto Balubeto]

So, in Apps Script, what function should I set instead of OnChange?

Thanks

Bye

[Brent Guttmann]

I don't understand what you are asking. The onChange event is handled in the triggers, as I explained previously. This was working as intended. If you edit the code or make changes to the sheet structure/format yourself, you should be prepared to resolve any errors created by those changes.

Keith has graciously offered to help, but you need to answer the following questions for him to assist you:

• What was the goal of the action you were performing when the error occurred?
• What did you expect to happen?
• What was the error exactly?

On Tue, Nov 11, 2025 at 1:14 PM Balubeto Balubeto <balu...@gmail.com> wrote:

So, in Apps Script, what function should I set instead of OnChange?

Thanks

Bye

Il giorno martedì 11 novembre 2025 alle 18:39:24 UTC+1 Brent Guttmann ha scritto:

You cannot run onChange from the appscript editor, it expects an event (e) to be passed -- onChange(e) 

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/49769d58-65f1-47b3-86e7-330241ac6187n%40googlegroups.com.

[Balubeto Balubeto]

When I run the script, the error rate is 25.53%. How come?

When I run it in Apps Script with the onOpen function set, I get the error “Exception: Cannot call SpreadsheetApp.getUi() from this context.” How come?

However, when I run it from the spreadsheet menu, the script seems to run correctly, but at the end of its execution, the error “ReferenceError: v is not defined” always appears. Why?

So, what should I do to have an error-free script?

Thanks

Bye

[Brent Guttmann]

I dont understand what you are running.

When I left it, it ran by itself automatically when you inserted a row. That is the entire purpose of the onChange event. There was no benefit or reason to run this script manually.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/98a1bc3b-eafa-45cd-82d5-79f7bdba2d38n%40googlegroups.com.

[Balubeto Balubeto]

Try running the entire script from the spreadsheet menu "Formattazione colori categorie spesa" and you will see the error message “ReferenceError: v is not defined.”

Thanks

Bye

[Keith Andersen]

What exactly, and please make the effort to explain and be specific, is your purpose for the script?  Please tell me like we're starting at square one ...what it is that you need the script to do on your spreadsheet?  It is only then that I, or anyone else, can formulate a process to achieve your goal. It is too far for us to go back and look through several different emails and the numerous emails in this thread to try and figure it out on our own.... We need you to explain it. 

Once you explain it fully and we get an understanding of what the script is supposed to do, we can formulate scripts to accomplish that goal through a tested process. Brent has already done that... but we need to understand the process.

Does that make sense to you? Do you have any questions?

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/b2a13a6e-7dc0-42c6-8d6b-4572d91a847an%40googlegroups.com.

[Balubeto Balubeto]

The script works, but when I run it from the spreadsheet menu "Formattazione colori categorie spesa", the error message “ReferenceError: v is not defined” is displayed.

Why is this happening? How can I fix the problem?

[Keith Andersen]

It appears that per your original request - you wanted format changes to be applied AUTOMATICALLY when certain changes were made to the spreadsheet.

Brent's script solution was to incorporate onEdit / onChange functions to AUTOMATICALLY make those changes AS YOU REQUESTED.

Therefore - there is no need or reason to run the "Formattazione colori categorie spesa" from the Menu. Your solution (from Brent) is not designed to be run manually - through the menu.  DON'T RUN "Formattazione colori categorie spesa"!

Keith

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/229b7e19-02d0-4398-9d2f-caa4412005fen%40googlegroups.com.

--

My Site: https://www.sites.google.com/view/klaweb/

Passions: God, Family, Friends, Scripture, Data Management, Google Sheets + App Script, MS Access, Programing, sharing and much more.

[Balubeto Balubeto]

So, can I ignore the error messages mentioned above?

Thanks

Bye

[Keith Andersen]

DON'T RUN IT MANUALLY FROM THE MENU. Then you won't get the error messages. In fact- delete the custom menu or de-activate it.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/f6938f0a-bf87-4f76-a90e-c427ff43d8b2n%40googlegroups.com.

[Balubeto Balubeto]

A curiosity: If I insert this script into a new spreadsheet with the same structure, how can I apply all the features of this script?

Thanks

Bye

[Balubeto Balubeto]

I noticed something:
The constant C_TOTAL = ‘#ffffff’ does not work because the cells of the rows 47, 107, and 129 are not all white but yellow. How come?

Thanks

Bye

[Balubeto Balubeto]

In other words, I would like the color of the cells in the rows containing the words “Totale mensile” in column A should be white, as it was a month ago.

Thanks

Bye

[Balubeto Balubeto]

How could I solve the problems mentioned above?

Thanks

Bye

[Balubeto Balubeto]

When you have time, can you still help me?

Thanks

Bye

[Keith Andersen]

Did those rows change color 1 time? Do they still charge colors?

Why not manually reset this rows that appeared to have randomly changed... And then look for the process that might change them or does change them randomly. 

Right now I have no idea why those particular few rows changed color.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

[Balubeto Balubeto]

 I would like the color of the cells in the rows containing the words “Totale mensile” in column A should be white.

Thanks

Bye

[Brent Guttmann]

I fixed this.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/11138cee-db84-4128-b8f8-7309cfeed458n%40googlegroups.com.

[Balubeto Balubeto]

 I would like the color of the cells in the rows containing the words “Totale mensile” in column A should be white to differentiate them from the others.

Thanks

Bye

[Balubeto Balubeto]

Thank you very much

Bye

[Balubeto Balubeto]

I noticed that, after Brent Guttmann's latest modification, I can also run the script from the spreadsheet menu without getting any errors.

Now, since I should create other similar projects, I would need the rows where the cells containing the words “Date e ricevute” in column A have the cells in columns B, D, F, H, J, L, N, P, R, T, V, and X colored in yellow with the blue writing, and those in columns C, E, G, I, K, M, O, Q, S, U, W, and Y colored in blue with the yellow writing.

Is it possible to do this change?

Thanks

Bye

[Brent Guttmann]

Sorry, but I am done helping on this.

[Balubeto Balubeto]

I noticed that the script does not automatically color the cells from column B to column Y when I add a new expense group (see the "Spese mediche” group).

How come?

Thanks

Bye

[Balubeto Balubeto]

Could you please help me again (see above)?

Thanks

Bye