I need Google Apps Script to perform the following:

1. Clone a Google Sheets file
2. In the cloned file, update the value of cell A1 to the current date and time (as value, not formula)
3. Rename the cloned file as the current date in format "YYYY-MM-DD"

I am struggling to get past section 2 after many hours to reading & testing - Please help (and let me know where I'm going wrong!)

function cloneGoogleSheet() {
  //1. clone file
  const destFolder = DriveApp.getFolderById("107TOUuO6fABxYohvueivNGeZqJMNkfgX"); //set destination folder in drive
  DriveApp.getFileById("16S3INZFMQDY3yguNZ2QHvzMQXI1Kf97DkSvcYyZeiHM").makeCopy("New File", destFolder); // clone source file into destination folder with name "New File"

  //2. set cell A1 on tab 1 to current date & time
  var sheet = SpreadsheetApp.open("New File").getSheets()[0] //open "New File"
  var range = sheet.getRange("A1"); // "set range in new file"
  range.setValue(now); // set the value in cell A1 to be current date & time (as text, not formula)

  //3. rename file so it files in year-month-date order
  var formattedDateForFileName = Utilities.formatDate(now(),"GMT 10","yyyy-mm-dd") //format date into YYYY-MM-DD (for filename)
  sheet.setName("Dashboard Archive - "& formattedDateForFileName) //rename file
}

CodePudding user response：

The funciton doesn't include the variable declaration for now.

You could add it as follow

const now = new Date();

Just put the above line before range.setValue(now);.

Also change now() by now per the previous suggestion and & by as in Google Apps Script / JavaScript the concatentio operator is , not &.

You might be trying to use in Google Apps Script what you know about Google Sheets functions and operators, but Google Sheets functions and operators aren't the same as those used in Google Apps Script / JavaScript.

Resources

• https://developers.googe.com/apps-script/guides/sheets

CodePudding user response：

In addition to what's Ruben said, I think it's a bit risky to get the new file with SpreadsheetApp.open("New File") because for many reasons you could have several spreadsheets with that name in your Drive.

I'd rather do

 let copy = DriveApp.getFileById("16S3INZFMQDY3yguNZ2QHvzMQXI1Kf97DkSvcYyZeiHM").makeCopy("New File", destFolder); // clone source file into destination folder with name "New File"

 //2. set cell A1 on tab 1 to current date & time
 let sheet = SpreadsheetApp.openById(copy.getId()).getSheets()[0] //open "New File"

In that way, you are 100% sure that you're opening the copy you just made