I have a google form and the responses are being recorded in a google sheet. I want to add some data processing to the cells following the responses, such as changing the timestamp to week number, timestamp as year, days since submission. So I have the formulas in my example row, row 2. And i'm trying to copy them into the last row with data.
| Timestamp | Formula 1 (Column L) | Formula 2 (Column M) | Formula 3 (Column N) |
|---|---|---|---|
| Timestamp 1 | formula 1 | formula 2 | formula 3 |
| Timestamp 2 | Blank | Blank | Blank |
In both attempts below I am getting the data validation copied, but the formulas don't copy and the cells remain blank. I first tried modifying an existing macro.
function Copypaste()
{
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
var lastrow = sheet1.getLastRow();
sheet1.getRange(lastrow, 12).activate();
sheet1.getRange(2, 12, 1, 15).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
sheet1.getRange(2, 12, 1, 15).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
};
And then I tried recording a Macro.
function CopyFormula() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('L12').activate();
spreadsheet.getRange('L2:Q2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
spreadsheet.getRange('L2:Q2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
};
As far as I can tell, they are essentially the same. I can run my recorded Macro and it works, but my modyified macro doesn't. Why?