'Script : Default values after edit a cell on a row

I already have a script here for when a cell on a row is edited, another cell on the same line will be filled with a timestamp. It also contains an option to overwrite and / or delete when the trigger-cell is changed /blanked out again.

It also works over different tab-sheets (January -> March in this example). I use exactly the same structure on each month. With max. 150 lines on each Month-tab.

Question:

I don't do any programming myself (Beginner: can change things when I see the code,...).

Is it possible to help me expanding this script so that not only in Column C the timestamp is filled in, but also columns D to G will be filled with data (Column D, F and G: data from a "settings-tab" and column D with the "total" in the header on same page). Trigger: after changing the value of column B.

I'll try to show my specific sheet here by 2 pics.

My sheet. (colmn B = onEdit-trigger / Columns C to G should be filled in)

My values on a settings-tab.

Code of the Script I use:

-> Working for passing date in Column C. Not working yet for also passing default-values (from settings-tab or total-value in header) to columns C, D, E, F, G after editing column B.

Can someone help me with this functionalty please?

Kind reagards!

Script:

/**
* Simple trigger that runs each time the user edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the script in the script editor window. It runs automatically when you edit the spreadsheet.');
  }
  defaultvalueMultipleColumns_(e);
}

function defaultvalueMultipleColumns_(e) {
  try {
    var ss = e.source;
    var sheet = ss.getActiveSheet();
    
    ////////////////////////////////
    // [START modifiable parameters]
    var defaultValueSettings = [
      {
        sheetsToWatch: /^(JAN|FEB|MRT)$/i,
        columnLabelsToWatch: ['Edit cell'],
        columnValuesToWatch: [undefined],
        columnLabelsToFill: ['Default Value 1'],
        overwriteFilledCells: [false],
        eraseFilledCells: [true],
        columnLabelRow: 8,
        defaultvalueFormat: 'dd/mm/yyyy'
      },
    ];
    // [END modifiable parameters]
    ////////////////////////////////

    if (!ss.getActiveRange() || JSON.stringify(e.range) === '{}') {
      return;
    }
    var sheetName = sheet.getName();
    for (var s = 0, numSettings = defaultValueSettings.length; s < numSettings; s++) {
      if (defaultValueSettings[s].sheetsToWatch.test(sheetName)) {
        var settings = defaultValueSettings[s];
        var columnLabelsToWatch = settings.columnLabelsToWatch;
        var columnValuesToWatch = settings.columnValuesToWatch;
        var columnLabelsToFill = settings.columnLabelsToFill;
        var overwriteFilledCells = settings.overwriteFilledCells;
        var eraseFilledCells = settings.eraseFilledCells;
        break;
      }
    }
    if (!settings || e.range.rowStart <= settings.columnLabelRow) {
      return;
    }
    if (!columnLabelsToWatch.length
        || columnLabelsToWatch.length !== columnValuesToWatch.length
        || columnLabelsToWatch.length !== columnLabelsToFill.length
        || columnLabelsToWatch.length !== overwriteFilledCells.length
        || columnLabelsToWatch.length !== eraseFilledCells.length) {
      throw new Error('Mismatch between the number of values in columnLabelsToWatch and columnValuesToWatch, columnLabelsToFill, overwriteFilledCells or eraseFilledCells.');
    }
    var columnLabels = sheet.getRange(settings.columnLabelRow, /*column*/ 1, /*numRows*/ 1, sheet.getLastColumn()).getValues()[0];
    var columnsToWatch = [];
    var columnsToStamp = [];
    for (var i = 0, col = 0, numColumns = columnLabelsToWatch.length; i < numColumns; i++) {
      if ((col = columnLabels.indexOf(columnLabelsToWatch[i])) > -1) {
        columnsToWatch.push(col + 1);
      }
      if ((col = columnLabels.indexOf(columnLabelsToFill[i])) > -1) {
        columnsToStamp.push(col + 1);
      }
    }
    if (columnsToWatch.length !== columnsToStamp.length) {
      throw new Error('Could not find as many columnLabelsToWatch as columnLabelsToFill.');
    }
    var modifiedValues = e.range.getValues();
    for (var row = 0, numRows = modifiedValues.length; row < numRows; row++) {
      for (var column = 0, numColumns = modifiedValues[row].length; column < numColumns; column++) {
        var modifiedValue = modifiedValues[row][column];
        var columnIndex = columnsToWatch.indexOf(e.range.columnStart + column);
        if (columnIndex === -1) {
          continue;
        }
        if (columnValuesToWatch[columnIndex] !== undefined && columnValuesToWatch[columnIndex] !== modifiedValue) {
          continue;
        }
        var defaultvalueCell = sheet.getRange(e.range.rowStart + row, columnsToStamp[columnIndex]);
        var defaultvalue
          = String(modifiedValue).length
          ? new Date()
          : eraseFilledCells[columnIndex]
          ? null
          : new Date();
        if (defaultvalue && !overwriteFilledCells[columnIndex] && defaultvalueCell.getValue()) {
          return;
        }
        defaultvalueCell.setValue(defaultvalue).setNumberFormat(settings.defaultvalueFormat);
      }
    }
  } catch (error) {
    showAndThrowError_(error.message + ', stack: ' + error.stack);
  }
}

/**
* Shows a message in a pop-up and throws an error with the message.
*
* @param {String} message The message to show and throw.
*/
function showAndThrowError_(message) {
  showMessage_(message, 30);
  throw new Error(message);
}

/**
* Shows a message in a pop-up.
*
* @param {String} message The message to show.
*/
function showMessage_(message, timeoutSeconds) {
  SpreadsheetApp.getActive().toast(message, 'defaultvalue multiple columns', timeoutSeconds || 5);
}


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source