Search code examples
google-apps-scriptgoogle-sheetsgoogle-forms

Can I replace the URL Link with specific text when using Google Script for the getEditResponeURL code?

I have used the below code in order to place the Google Form URL in to the spreadsheet But I would like to know if I can replace the URL which can be very long, with a hyperlink 'EDIT FORM' text.

var formURL = 'https://docs.google.com/forms/d/(MY CODE)/viewform';
var sheetName = 'Responses';
var columnIndex = 8 ;

function getEditResponseUrls() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = 2; i < data.length; i++) {
    if (data[i][0] != '' && data[i][columnIndex-1] == '') {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if (formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex).setValue(editResponseUrl);
    }
  }
}
Solution

  • In order to achieve what you want you just have to the following changes to your code:

    var editResponseUrl = formSubmitted[0].getEditResponseUrl();
var formula = '=HYPERLINK('+'"'+editResponseUrl+'"'+',"EDIT FORM")';
sheet.getRange(i+1, columnIndex).setFormula(formula);

    The above snippet uses the =HYPERLINK() formula in Sheets in order to be able to create the hyperlink of the editResponseUrl with the EDIT FORM text. Afterwards, this formula is used by using the setFormula() method in Apps Script.

    Reference