根据同一行中另一个单元格中的日期自动将特定文本插入Google电子表格单元格中的脚本 [英] Script to automatically insert a specific text in cells of a Google spreadsheet, based on a date in another cell, on the same row

问题描述

我在另一个

I have the following script provided by @ZektorH in another question of mine.

function onEdit(e) {
  if (e.range.getColumn() ==2) {
    //User edited the date column
    if (typeof e.range.getValue() === typeof new Date()) {
      //Value of edit was a date
      endColumns(e.range.getRow(), e.range.getValue());
    } else if (e.range.getValue() === ""  || e.range.getValue() === null) {
      var sheets = SpreadsheetApp.getActiveSheet();
      var resetRange = sheets.getRange(e.range.getRow(), e.range.getColumn()+1, 1, sheets.getMaxColumns()-e.range.getColumn());
      resetRange.clear({contentsOnly: true}); //Will delete all text, not only the "ENDED" text.
    }
  }
}

function endColumns(rowNum, limitDate) {
  var sheets = SpreadsheetApp.getActiveSheet();

  var colOffset = 3; //Offset to account for your row Headers
  var dateHeader = sheets.getRange(1, colOffset, 1, sheets.getMaxColumns()-colOffset);

  var availableDates = dateHeader.getValues()[0];

  var foundCol = 0;
  for (var i=0; i<availableDates.length; i++) {
    if (availableDates[i]>=limitDate) {
      break;
    }
    foundCol++;
  }

  var rewriteCells = sheets.getRange(rowNum, foundCol+colOffset, 1, sheets.getMaxColumns()-(foundCol+colOffset-1));

  //Add your formatting and text below:
  rewriteCells.setValue("ENDED");

  //Clear all cells that are "white" (no header)
  for (var i=0; i<availableDates.length; i++) {
    if (availableDates[i]==="" || availableDates[i] ===null) {
      sheets.getRange(rowNum, colOffset+i).clear();
    }
  }
}

少量修改后，脚本可以正常工作，但是仍然存在一个问题.当您清除一行中 date 单元格(列B)中的值时，脚本将清除该行所有单元格中的所有值.

The script is working correctly after some small edits, but there is still one problem. When you clear the value in the date cell (col B) on a row, the script will clear all values in all cells on that row.

这对我来说是个问题，因为许多行在ENDED日期之前的单元格中都有重要数据.

This is an issue for me because many rows have important data in the cells prior to the ENDED date.

问题

我需要在此脚本中进行哪些更改，以便在清除 date单元格后仅清除带有ENDED文本的单元格?

What do I need to change in this script so that it will only clear the cells with the ENDED text, after clearing the date cell?

推荐答案

我稍微修改了一下代码，基本上添加了一个条件来检查每个单元格中的值是否等于"ENDED"，并且仅在删除数据时才删除情况:

I've reworked your code a bit, basically adding a condition to check whether the value in each cell equals "ENDED", and only deleting data if that's the case:

// Delete all "ENDED" values:
var resetRange = sheets.getRange(e.range.getRow(), e.range.getColumn() + 1, 1, sheets.getMaxColumns() - e.range.getColumn());
var resetValues = resetRange.getValues()[0];
for(var i = 0; i < resetValues.length; i++) {
  if(resetValues[i] == "ENDED") {
    var resetCell = sheets.getRange(e.range.getRow(), e.range.getColumn() + 1 + i);
    resetCell.clearContent();
  }
}

此外，如果您编辑到以后的日期，则会删除相应的"ENDED"值:

And also this, which deletes corresponding "ENDED" values if you edit to a later date:

// Clear past dates if new date is later:
var beforeDate = sheets.getRange(rowNum, colOffset, 1, foundCol);
var beforeValues = beforeDate.getValues()[0];
for(var i = 0; i < beforeValues.length; i++) {
  if(beforeValues[i] == "ENDED") {
    sheets.getRange(rowNum, colOffset + i).clearContent();
  }  
}

因此，完整的代码如下:

So, full code would be like this:

function onEdit(e) {
  if (e.range.getColumn() == 2) {
    //User edited the date column
    if (typeof e.range.getValue() === typeof new Date()) {
      //Value of edit was a date
      endColumns(e.range.getRow(), e.range.getValue());
    } else if (e.range.getValue() === ""  || e.range.getValue() === null) {
      var sheets = SpreadsheetApp.getActiveSheet();
      // Delete all "ENDED" values:
      var resetRange = sheets.getRange(e.range.getRow(), e.range.getColumn() + 1, 1, sheets.getMaxColumns() - e.range.getColumn());
      var resetValues = resetRange.getValues()[0];
      for(var i = 0; i < resetValues.length; i++) {
        if(resetValues[i] == "ENDED") {
          var resetCell = sheets.getRange(e.range.getRow(), e.range.getColumn() + 1 + i);
          resetCell.clearContent();
        }
      }      
    }
  }
}

function endColumns(rowNum, limitDate) {
  var sheets = SpreadsheetApp.getActiveSheet();

  var colOffset = 3; //Offset to account for your row Headers
  var dateHeader = sheets.getRange(1, colOffset, 1, sheets.getMaxColumns() - colOffset);

  var availableDates = dateHeader.getValues()[0];

  var foundCol = 0;
  for (var i = 0; i < availableDates.length; i++) {
    if (availableDates[i] >= limitDate) {
      break;
    }
    foundCol++;
  }

  var rewriteCells = sheets.getRange(rowNum, foundCol + colOffset, 1, sheets.getMaxColumns() - (foundCol + colOffset - 1));

  //Add your formatting and text below:
  rewriteCells.setValue("ENDED");

  // Clear past dates if new date is later:
  var beforeDate = sheets.getRange(rowNum, colOffset, 1, foundCol);
  var beforeValues = beforeDate.getValues()[0];
  for(var i = 0; i < beforeValues.length; i++) {
    if(beforeValues[i] == "ENDED") {
      sheets.getRange(rowNum, colOffset + i).clearContent();
    }  
  }


  // Clear all cells that are "white" (no header)
  for (var i = 0; i < availableDates.length; i++) {
    if (availableDates[i] === "" || availableDates[i] === null) {
      sheets.getRange(rowNum, colOffset+i).clear();
    }
  }
}

我希望这对您有帮助.

这篇关于根据同一行中另一个单元格中的日期自动将特定文本插入Google电子表格单元格中的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！