How do I select a Google Sheets cell by its string value and delete it in Google Apps Script?-CodePudding

I am trying to create a function in Google Apps Script for a Google Sheet containing data that will automatically be updated every month. I want a cell in column A with value '1/30/3800' to be deleted (i.e. I want to set its value to ''.) I cannot for the life of me figure out how to specify the cell based on its string value, NOT based on its position in the spreadsheet. I've tried these two methods:

function forecastUpdate() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheets()[1]
  let data = sheet.getDataRange().getValues()

  for (let i = 0; i <- data.length; i  ) {
    if (data[i] === '1/30/3800') {
      data[i] = '';
    }
  }
}

function forecastUpdate() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheets()[1]
  let data = sheet.getDataRange().getValues()

  for (let value of data) {
    if (value === '1/30/3800') {
      value = '';
    }
  }
}

It says it executes, but it doesn't - nothing updates in my sheet. Any tips?! Note 1: The cell that I am trying to clear has a value derived from a QUERY formula in the Google Sheet. Not sure if that matters. Note 2: I cannot select it by its position (A41) in the sheet because its position will change every month.

CodePudding user response：

You have to use Range.setValue . Also data is a 2D array here. So you need to get the value correctly.

Assuming that the value '1/30/3800' is a string and not interpreted as a date, the following should work.

function forecastUpdate() {
    let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spreadsheet.getSheets()[1];
    let data = sheet.getDataRange().getValues();

    let foundAtRow;

    for (let i = 0; i < data.length; i  ) {
        let row = data[i];
        let colA = row[0];
        if (colA === '1/30/3800') {
            foundAtRow = i   1;
            break;
        }
    }

    if (!foundAtRow) {
        throw new Error('Match not found.');
    }

    sheet.getRange('A'   foundAtRow).setValue('');
}

Depending on the formula, you may not be allowed to update the cell value

CodePudding user response：

Function occurs once month using a daily trigger it looks for strings in column equal to '1/30/3800' and sets the value of each cell to null.

function forecastUpdate(e) {
  if (e['day-of-month'] == 1) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sh = ss.getSheetByName('Sheet0');
    const sr = 2;//data start row
    const vs = sh.getRange(sr, 1, sh.getLastRow() - sr   1, 1).getDisplayValues()
    vs.forEach(r => {
      if (r[0] == '1/30/3800') {
        r[0] = '';
      }
    });
    sh.getRange(sr, 1, vs.length, vs[0].length).setValues(vs)
  }
}


function createDailyTrigger() {
  if (ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == 'forecastUpdate').lendth == 0) {
    ScriptApp.newTrigger('forcastUpdate').timeBased().everyDays(1).atHour(0).create();
  }
}

Time Based Event Object

Array.forEach