How do I get the URL for a Google Sheets file that I just created using the copy function

In my ongoing efforts to come to grips with google scripts: I am trying to loop through a list of items in a sheet - wherever there is a "X" in column A, I want to copy my active file and then save the URL of the new file into my active file. I've figured out most of it with the help of my second brain (otherwise known as google) and a helpful person on this forum, but I'm stuck on how to get the URL of the new file and save it to my sheet. While I'm at it, I'd also like to save the current date into column B of the same sheet. Here is the code so far:

function LoopSaveSend() {

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("4 Week Cycle");
  var lastRow = activeSheet.getLastRow();
  var lastCol = activeSheet.getLastColumn();

  var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Log");
  var lastSourceRow = sourceSheet.getLastRow();
  var lastSourceCol = sourceSheet.getLastColumn();

  var sourceRange = sourceSheet.getRange(1, 1, lastSourceRow, lastSourceCol);
  var sourceData = sourceRange.getValues();

  var activeRow = 0;

  //Loop through every retrieved row from the Source
  for (row in sourceData) {
    //IF Column A in this row has 'X', then work on it.
    if (sourceData[row][0] == 'X') {
      //Save it to a temporary variable
      var tempvalue = [sourceData[row][2]];
      activeSheet.getRange('rfInitials').setValue(tempvalue);
      var spreadsheet = SpreadsheetApp.getActive();
      var rffilename = spreadsheet.getRange('rfFileName').getValue();
      spreadsheet.copy(rffilename);
      var url = SpreadsheetApp.openById(rffilename).getUrl();
      sourceData[row][4].setValue(url);
      sourceData[row][0].setValue("")
   }
  }
}

It's the last 3 lines that aren't working (I don't even know if the last 2 rows will work because I haven't got that far yet.) The error I'm getting is that I need permission to open the file - but actually I don't want to open it - just want to get the URL. Also - no code for getting the date yet... baby steps and all that. Any assistance greatly appreciated.

CodePudding user response：

To get the URL of the newly copied spreadsheet, change these two lines:

spreadsheet.copy(rffilename);
var url = SpreadsheetApp.openById(rffilename).getUrl();

to this single line:

var url = spreadsheet.copy(rffilename).getUrl()

The key here is that, per the documentation, the copy method

Copies the spreadsheet and returns the new one.

So you can call .getUrl() directly on the result of copy to get the URL of the new copy.

The reason this line doesn't work as written:

var url = SpreadsheetApp.openById(rffilename).getUrl();

is that rffilename is not a file id, it's a file name. To use .openById you would need to know the file id of the new copy (which you could get with spreadsheet.copy(rffilename).getId() but in this case you don't need to).

You will also find that your last two lines don't work, as .setValue is a method of the Range object. You'll need to change these to something like

activeSheet.getRange(row,4).setValue(url);
activeSheet.getRange(row,0).setValue("")