I'm trying to fully automate a keyword report on Google Sheet.
I export my data on a monthly basis from an API called Supermetrics.
My script below add a new column (after F) an copy the data to the newly created column (Only the 'date' part of the script isn't working).
I'd like the header of this column to be the date of my data export.

1. How can I name my column by the date I want (which is contains in cell C18)?
2. How can I edit my script so that my column has the name of a particular date (let say 1st of each month)?

Below the link to my Google sheet and the script. Thanks a lot in advance for your help!
https://docs.google.com/spreadsheets/d/1ZD-3r3sceK0H7Q17agyZqVggH3D6utnEFPy69aP2wUg/edit#gid=0

function insertColumn() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('KW');
  var date = new Date();
  var valuestocopy = sheet.getRange(2, 6, 14, 1)
  sheet.insertColumnAfter(6).getRange(1,15).setValue(date).setNumberFormat("DD.MM.YY");
  valuestocopy.copyValuesToRange(sheet, 7, 7, 2, 15);
  }

CodePudding user response：

I noticed that your code didn't work because of your usage of the getRange() function in line 6. What your code did was to point at the cell in row 1 column 15 and to insert the date in this cell. So what I did was to point the function to the desired cell (G1).

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('KW');
  var date = new Date();
  var valuestocopy = sheet.getRange(2, 6, sheet.getLastRow(), 1);
  sheet.insertColumnAfter(6).getRange("G1").setValue(date).setNumberFormat("DD/MM/YY");
  valuestocopy.copyValuesToRange(sheet, 7, 7, 2, sheet.getLastRow());
  sheet.getRange(1,6,sheet.getLastRow(),1).clearContent();
}

With this code, the timestamp is already generated from the script and pasted on row 1 of the newly inserted column instead of copying the data stored in cell C18 which contains the current timestamp as well.

Sheet before the application of the code: https://i.stack.imgur.com/DEBat.png

Sheet after the application of the code: https://i.stack.imgur.com/Xgzup.png

Reference: copyValuesToRange() getRange(a1Notation) getLastRow()

Edit: I added the sheet.getLastRow() option during range selection so that it can adjust based on the data that needs to be copied.