I have a Google Apps Script that creates a blinking effect in Column B (rows 1-100) of my Google Sheet. The script toggles the background color and text of these cells to create a blinking effect. When I edit a cell in Column B, I expect the blinking to stop for that specific row, and the value I entered to be preserved. However, after editing a cell, the blinking effect sometimes resets the cell's value back to the blinking text. Here is my Google Apps Script code:

var blinkingRows = {};
var isBlinking = false;
var targetSheetName = "sheet3";
var editedValues = {}; // Store edited values

function onOpen() {
    startBlinking();
}

function onEdit(e) {
    var sheet = e.source.getActiveSheet();
    if (sheet.getName() !== targetSheetName) return;

    var cell = e.range;

    if (cell.getColumn() === 2 && cell.getRow() <= 100) {
        blinkingRows[cell.getRow()] = false;
        var userValue = cell.getValue();
        editedValues[cell.getRow()] = userValue; // Store edited value
        cell.setBackground("white");
        SpreadsheetApp.flush();
    }
}

function startBlinking() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);
    if (!sheet) return;

    for (var row = 1; row <= 100; row++) {
        blinkingRows[row] = true;
    }

    if (!isBlinking) {
        isBlinking = true;
        blinkLoop();
    }
}

function blinkLoop() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);
    if (!sheet) {
        isBlinking = false;
        return;
    }

    var colors = ["purple", "white"];
    var texts = ["om", ""];
    var i = 0;

    function innerLoop() {
        if (!isBlinking) return;

        for (var row = 1; row <= 100; row++) {
            var cell = sheet.getRange(row, 2);

            if (blinkingRows[row] === true) {
                cell.setValue(texts[i % 2]);
                cell.setBackground(colors[i % 2]);
            }
        }

        // Apply edited values after the loop
        for (var row in editedValues) {
            if (editedValues.hasOwnProperty(row)) {
                sheet.getRange(parseInt(row), 2).setValue(editedValues[row]);
                delete editedValues[row]; // Clear the stored value
            }
        }

        SpreadsheetApp.flush();
        i++;
        Utilities.sleep(1000);
        innerLoop();
    }

    innerLoop();
}

I have a Google Apps Script that creates a blinking effect in Column B (rows 1-100) of my Google Sheet. The script toggles the background color and text of these cells to create a blinking effect. When I edit a cell in Column B, I expect the blinking to stop for that specific row, and the value I entered to be preserved. However, after editing a cell, the blinking effect sometimes resets the cell's value back to the blinking text. Here is my Google Apps Script code:

var blinkingRows = {};
var isBlinking = false;
var targetSheetName = "sheet3";
var editedValues = {}; // Store edited values

function onOpen() {
    startBlinking();
}

function onEdit(e) {
    var sheet = e.source.getActiveSheet();
    if (sheet.getName() !== targetSheetName) return;

    var cell = e.range;

    if (cell.getColumn() === 2 && cell.getRow() <= 100) {
        blinkingRows[cell.getRow()] = false;
        var userValue = cell.getValue();
        editedValues[cell.getRow()] = userValue; // Store edited value
        cell.setBackground("white");
        SpreadsheetApp.flush();
    }
}

function startBlinking() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);
    if (!sheet) return;

    for (var row = 1; row <= 100; row++) {
        blinkingRows[row] = true;
    }

    if (!isBlinking) {
        isBlinking = true;
        blinkLoop();
    }
}

function blinkLoop() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);
    if (!sheet) {
        isBlinking = false;
        return;
    }

    var colors = ["purple", "white"];
    var texts = ["om", ""];
    var i = 0;

    function innerLoop() {
        if (!isBlinking) return;

        for (var row = 1; row <= 100; row++) {
            var cell = sheet.getRange(row, 2);

            if (blinkingRows[row] === true) {
                cell.setValue(texts[i % 2]);
                cell.setBackground(colors[i % 2]);
            }
        }

        // Apply edited values after the loop
        for (var row in editedValues) {
            if (editedValues.hasOwnProperty(row)) {
                sheet.getRange(parseInt(row), 2).setValue(editedValues[row]);
                delete editedValues[row]; // Clear the stored value
            }
        }

        SpreadsheetApp.flush();
        i++;
        Utilities.sleep(1000);
        innerLoop();
    }

    innerLoop();
}

• google-sheets
• google-apps-script
• triggers

Share Follow edited Mar 9 at 6:02 TheMaster 51.3k77 gold badges7373 silver badges100100 bronze badges asked Mar 9 at 2:11 R SR S 111 silver badge

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Your script can be simplified and optimized by making one call to get the values in B1:B100 and one call to set the background colors during each blink.

To handle the 30-second execution time limit, install this function as an installable trigger that triggers with "on Open".

var targetSheetName = "Sheet1"
var targetRange = 'B1:B100'

function blinkLoop() {
    var sheet = SpreadsheetApp.getActive().getSheetByName(targetSheetName)

    // check if target sheet exists
    if (!sheet) return

    // get the target range
    var range = sheet.getRange(targetRange)

    var i = 0

    for (var i = 0; i <= 50; i += 1) {
      // retrieve the current values
      var values = range.getValues().map(x => (!x[0] || x[0] == 'om') ? ['om'] : x)

        if (i % 2) {
          // retrieve the current values
          var values = range.getValues().map(x => !x[0] ? ['om'] : x)
          // loop over values: color when value exists, else null
          var backgrounds = values.map(x => x[0] == 'om' ? ['purple'] : [null])
          // set all background colors
          range.setBackgrounds(backgrounds)
        } else {
          // retrieve the current values
          var values = range.getValues().map(x => x[0] == 'om' ? [null] : x)
          // clear all backgrounds by setting the color to null
          range.setBackground(null)
        }

        range.setValues(values)

        SpreadsheetApp.flush()
        Utilities.sleep(1000)
    }

    range.setBackground(null)
}