• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • About
  • Life
  • Tech
  • Travel
  • Work
  • Questions
  • Contact

Welcome

.

I want delete a cell when I delete another one with this script in googlesheets

April 11, 2020 by

Questions › I want delete a cell when I delete another one with this script in googlesheets
0
Vote Up
Vote Down
Garmaine asked 3 years ago

When I edit a cell ("active cell"), the actual date set in another one ("celltoedit"). But when I delete the original one, "celltoedit" doesn't.

It's my first script, so I need help for dummies, please.

I have problems with the last part. With this code when I edit a cell, actual date appear in another one, but after this, both are automatically deleted.

The code is this:

//Some functions to get the Letter or 'vice versa'
function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function letterToColumn(letter) {
  var column = 0,
    length = letter.length;
  for (var i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}
//Source: https://stackoverflow.com/a/21231012/8586908

// When a cell is modified on the spreadsheet this function gets called.

function onEdit(e) {
  //We get the spreadsheet object
  var sheet = e.range.getSheet();

  //Save currently activeCell
  var activeCell = SpreadsheetApp.getActiveRange();
  var column = activeCell.getColumn();
  var row = activeCell.getRow();

  //If the cell is empty probably the user has just deleted it so we don't have to put the date
  if (activeCell.getValue() !== "") {

    //We get the cell we want to edit
    var cellToEdit = sheet.getRange(columnToLetter(column + 33) + (row)).getCell(1, 1)

    //If the cell is empty
    if (cellToEdit.getValue() == "") {
      //We edit the cell to save the actual date, just if cell is in interested sheet and range.
      if (e.range.getSheet().getName() != '1ºESO') return; //we put here this to limit its operation to a single sheet using Name's sheet.
      if (e.range.columnStart >= 9 && e.range.columnStart <= 14 && e.range.rowStart >= 9 & e.range.rowStart <= 2653) { //Define interested range here: I9:N2653
        cellToEdit.setValue(new Date());

        //If the cell is deleted
        if (activeCell.getValue() !== "") {

          //and the edition is in range
          if (e.range.getSheet().getName() != '1ºESO') return; //we put here this to limit its operation to a single sheet using Name's sheet.
          if (e.range.columnStart >= 9 && e.range.columnStart <= 14 && e.range.rowStart >= 9 & e.range.rowStart <= 2653) { //Define interested range here: I9:N2653

            //we delete cell with date
            cellToEdit.setValue("");
          }
        }
      }
    }
  }
}
Are you looking for the answer?
Original Question and Possible Answers can be found on `http://stackoverflow.com`

Question Tags: google-sheets, javascript

Please login or Register to submit your answer




Primary Sidebar

Tags

Advancements best Business strategies commercial convenience economic Finances Cognitive decline Financial growth firm Future Hidden Gems Home hydration Impact Innovations lighting line of work Mental health Must-See New York City office patronage Productivity profession Profitability tips Profit optimization pursuit recreation Revenue enhancement romance sippy cups social station Technological breakthroughs technology toddlers trading transaction Treasures Uncover undertaking Well-being Wonders Work Young onset dementia

Newsletter

Complete the form below, and we'll send you all the latest news.

Footer

Footer Funnies

Who knew that reading the footer could be such a hilarious adventure? As we navigate websites, books, and documents, we often stumble upon the unassuming space at the bottom, only to discover a treasure trove of amusement. In this side-splitting compilation, we present 100 jokes that celebrate the unsung hero of content – the footer. Get ready to chuckle, giggle, and maybe even snort as we dive into the world of footnotes, disclaimers, and hidden comedic gems. Brace yourself for a wild ride through the footer!

Recent

  • Unveiling the Enigma: Almost-Magical Lamp Lights Highway Turns
  • The Impact of Young Onset Dementia on Employment and Finances: Optimizing Post-Diagnostic Approaches
  • 11 Wonders of 2023 Technological Breakthrough – Unveiling the Future
  • Work from Home and Stay Mentally Sane – Achieve Productivity and Well-being
  • Hidden Gems of New York City – Uncover the Must-See Treasures!

Search

Tags

Advancements best Business strategies commercial convenience economic Finances Cognitive decline Financial growth firm Future Hidden Gems Home hydration Impact Innovations lighting line of work Mental health Must-See New York City office patronage Productivity profession Profitability tips Profit optimization pursuit recreation Revenue enhancement romance sippy cups social station Technological breakthroughs technology toddlers trading transaction Treasures Uncover undertaking Well-being Wonders Work Young onset dementia

Copyright © 2023