'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)
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 |
|---|
