@Darren_Murphy Might you help me. What values should I substitute in this scripts?
I understand that by default I must leave row 2, which is where the formula will be copied from.
Now my logic tells me (I have no experience in script) that I should replace the value of the column where the formula is and the name of the sheet where the new row will be created so that everything works.
I understand what those 2 variables would be. If the source row is by default in 2, I would only have to change the name of the sheet.
But the sequence of this example doesn’t match the script below, I don’t see an option in this script to rename the sheet.
function copyFormulasDown(sheet, sourceRow) {
if (sourceRow === undefined) {
sourceRow = 2;
}
var formulas = getFormulas_(sheet, sourceRow);
if (formulas !== {}) {
var rows = sheet.getDataRange().getFormulas();
for (var r = rows.length - 1; r >= sourceRow && rows[r].join('') === ''; r--) {
copyFormulas_(sheet, r, formulas);
}
}
}
Could you help me to know in this same example where I should change the name of the sheet?
Note: I leave the complete example below. I took it from an example you gave.
/**
* Copy Formulas Down
* This script copies functions from a source row (usually the first data row in a spreadsheet) to
* rows at the end of the sheet, where the colums for these formulas are still empty.
* It checks row from the bottom upwards, copying formulas into the rows where there are no formulas yet,
* and stops as soon as it finds a row which has a formula already.
* When copying formulas, it leaves values in other cells unchanged.
*/
/**
* Copy formulas from the a source row in a sheet to rows below
* @param {Sheet} sheet Google Spreadsheet sheet
* @param {int} sourceRow (optional) 1-based index of source row from which formulas are copied, default: 2
*/
function copyFormulasDown(sheet, sourceRow) {
if (sourceRow === undefined) {
sourceRow = 2;
}
var formulas = getFormulas_(sheet, sourceRow);
if (formulas !== {}) {
var rows = sheet.getDataRange().getFormulas();
for (var r = rows.length - 1; r >= sourceRow && rows[r].join('') === ''; r--) {
copyFormulas_(sheet, r, formulas);
}
}
}
/**
* Copy formulas into row r in sheet
* @param {Sheet} sheet Google Spreadsheet sheet
* @param {int} r 1-based index of row where formulas will be copied
* @param {array} formulas array of objects with column index and formula string
*/
function copyFormulas_(sheet, r, formulas) {
for (var i = 0; i < formulas.length; i++) {
sheet.getRange(r + 1, formulas[i].c + 1).setFormulaR1C1(formulas[i].formula);
}
}
/**
* Read formulas from the source row, creating an array of objects
* Each objects contains the column index and formula string
* @param {Sheet} sheet Google Spreadsheet sheet
* @param {int} r 1-based index of source row
* @return {array} array of objects
*/
function getFormulas_(sheet, r) {
var row = sheet.getRange(r, 1, 1, sheet.getLastColumn()).getFormulasR1C1()[0];
var formulas = [];
for (var c = 0; c < row.length; c++) {
if (row[c] !== '') {
formulas.push({
c: c,
formula: row[c]
});
}
}
return formulas;
}
It doesn’t go inside the script, it goes outside the script. Let me explain a little more…
This type of function is often referred to as a “helper function”, or a “utility function”. The general idea is that you write a function in a generic way such that it can be used over and over again.
So, let’s say you want to use it on 3 different sheets - “Sheet 1”, “Sheet 2” and “Sheet 3”. You might write something like this:
So in the above case, you call the function once for each of your 3 sheets. But a better way to do that would be to create an array of sheet names, and then use an iterator, like so:
var sheetnames = ["Sheet 1", "Sheet 2", "Sheet 3"];
sheetnames.forEach(function (name) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name);
copyFormulasDown(sheet, 2);
});
The important point is that you can use the same function (copyFormulasDown) on several different sheets without the need to modify or copy the original function. This is called DRY programming
Thanks a lot. I already got it to work for a single sheet and managed to set the trigger. Tomorrow I will be trying so that it can be used for more leaves that I need, any questions I ask for your help hahaha.
Hi @Darren_Murphy. Shame to bother you, last night I set the script this way as I show you in the attached image and it worked fine for a single sheet.
Today I have tried to adapt the code that you have given me so that it functions in several sheets and I have not been able to and when I return to the same code that worked I get this error.
Yes, you’re calling the function from within itself, and creating a circular reference. Please go back and read carefully what I told you before. In particular, this
Thank you @Darren_Murphy . I will keep trying, I am costing more than normal because I have no experience in script but I know that I will be able to configure it.
I’m glad my advice helped. I have had to redesign my system at least 5 times… but I was doing unusual things from the beginning and coming into situations that are common for transactional business apps, such as ‘race conditions’ where Glide does not natively handle this situation very well.
Row Owners is another thing to get your head around as a general issue. It does make some things just a little more tricky!!
Could you help me with this? I am new to JavaScript and is still learning. The line where it says fix_timestamps(sheetname, [36]) I am getting an error message. Are you able to assist me with this?
That’s quite odd - I can’t see why it would be throwing that error.
Would you be able to share a copy of your Google Sheet with me so that I can take a look?
Let me double-check with my boss to make sure it’s okay to share with you. If that doesn’t work, I may be able to just send screenshots also. But i’ll let you know as soon as possible.
I’m not sure that screenshots will help. The reason I asked for a copy is so that I can see the script in its entirety and execute it myself. If it makes it any easier:
make a copy of the Google Spreadsheet
delete all sheets from the copy except the one referred to in the script
remove any potentially sensitive data from that sheet