My function takes a range from a source URL and 'pastes' the values into a range in a destination sheet.

The problem is .openByUrl only works if I set the variable in the function. Same error if I use .openById

If I run the function inputPrompt() and and pass the URL pasted into the prompt, I get "Exception: Invalid argument: url".

If I run exactly the same code, but give the exact same URL as a string, it works perfectly.

What am I missing?

function getSourceDataFromURL(urlString) {
// If I uncomment the line below, it works perfectly.  
//urlString = "https://docs.google.com/spreadsheets/d/1b6fOAWdJeVrHPY2UDPmx4VNPn_qJ__5daGkj4_FzynI/edit#gid=0"
  var sourceData = SpreadsheetApp.openByUrl(urlString).getSheetByName("Exporter").getRange("C3:AC3").getValues(); //AC3
  return sourceData
}

function inputPrompt(){
  var ui = SpreadsheetApp.getUi()
  var result = ui.prompt("Create row from URL", "Paste the URL here",ui.ButtonSet.OK_CANCEL)
  var button = result.getSelectedButton()
  var urlString = result.getResponseText()
  if (button == ui.Button.OK) {  
    var importedSourceData = getSourceDataFromURL(urlString)
    createRowFromImportedData(importedSourceData)
    } else if (button == ui.Button.CANCEL) {
  } else if (button == ui.Button.CLOSE) {
  }
}

CodePudding user response：

Cannot replicate the problem:

function openupanotherss() {
  const ss = SpreadsheetApp.getActive();
  const r = SpreadsheetApp.getUi().prompt("Url", "Enter Url", SpreadsheetApp.getUi().ButtonSet.OK);
  if (r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK) {
    let url = r.getResponseText();
    const dss = SpreadsheetApp.openByUrl(url);
    const sh = dss.getSheetByName("Sheet0");
    Logger.log(JSON.stringify(sh.getDataRange().getValues()));
  }
}