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?

0

There are 0 best solutions below