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.