How to retrieve redirect URL from Google Sheets using Google Apps Script?

Other Questions Google Sheets
1

I need help with getting a redirect URL from Google Sheets using Apps Script. I have two fitness forms - “Training Form X” and “Training Form Y”. Form X should automatically redirect to Form Y after submission but it’s not working properly.

I created a script that builds a URL and puts it in the last cell of my sheet. I want to grab that URL dynamically and use it for redirecting from Form X, but I keep getting errors.

URL Generation Script:

function onFormSubmit(e) {
  createPrefilledURLs();
}

function createPrefilledURLs() {
  const worksheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const allData = worksheet.getDataRange().getValues();
  const headerRow = allData[0];

  const dateColumn = headerRow.indexOf("Exercise Date");
  const categoryColumn = headerRow.indexOf("Exercise Category");

  if (dateColumn === -1 || categoryColumn === -1) {
    throw new Error("Required headers 'Exercise Date' and/or 'Exercise Category' missing.");
  }

  const formURL = "https://docs.google.com/forms/d/e/1FAIpQLScojqj2IUfZSpyOATMsJKuTbr_iSGJ3NginEBY4wCwWddp8-A/viewform";
  const urlColumn = headerRow.length + 1;
  worksheet.getRange(1, urlColumn).setValue("Generated Form Link");

  const finalRow = allData.length - 1;
  const exerciseDate = encodeURIComponent(allData[finalRow][dateColumn]);
  const exerciseCategory = encodeURIComponent(allData[finalRow][categoryColumn]);

  const finalURL = `${formURL}?entry.9876543210=${exerciseDate}&entry.1357924680=${exerciseCategory}`;
  worksheet.getRange(finalRow + 1, urlColumn).setValue(finalURL);
}

When I submit Form X, it creates the URL in the bottom right cell. I want to use that URL to update Form X’s redirect settings so it automatically fills Form Y.

Redirect Retrieval Script:

function doGet() {
  const worksheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sheetData = worksheet.getDataRange().getValues();

  if (sheetData.length < 2) {
    return HtmlService.createHtmlOutput("No data available.");
  }

  const bottomRow = sheetData.length - 1;
  const rightmostCol = sheetData[0].length - 1;
  const targetURL = sheetData[bottomRow][rightmostCol].toString().trim();

  Logger.log("Retrieved URL: " + targetURL);

  const redirectHTML = `
    <html>
      <head>
        <meta http-equiv="refresh" content="0; url=${targetURL}" />
      </head>
      <body>
        <p>Loading exercise form...</p>
      </body>
    </html>
  `;

  return HtmlService.createHtmlOutput(redirectHTML);
}

I deployed the script with “anyone with link” access and set execution as “me”. The URL generates correctly but I get a blank screen when trying to redirect. Previously I was getting invalid value errors. Any ideas what might be wrong?

2

Had the same issue with form redirects and Apps Script. It’s probably a timing thing - your onFormSubmit trigger creates the URL but there’s a delay before it hits the sheet. When doGet runs right after, it’s grabbing the previous row’s data or an empty cell. Try making your doGet function look for the most recent timestamp instead of just the bottom row. Also, Google Forms has built-in redirect stuff that might work better than a custom web app. In your Form X settings, you can set a confirmation message with a link or use ‘Go to page based on answer’ to redirect to Form Y automatically. If you stick with the web app, add error handling to check if the URL starts with ‘https://’ before redirecting. That’ll catch empty or broken URLs that cause blank screens.

3

your targetURL is prob empty or has whitespace. add some debugging first - change your doGet to return HtmlService.createHtmlOutput("URL: " + targetURL) so u can see what’s actually getting pulled. also check if that cell has a formula instead of the actual URL value - that’ll break things.

4

The blank screen happens because HTML meta refresh doesn’t work well with Google Apps Script’s HtmlService. I’ve hit this same issue with web app redirects. Skip the meta refresh and use JavaScript instead: javascript const redirectHTML = ` <html> <body> <p>Redirecting...</p> <script> window.top.location.href = "${targetURL}"; </script> </body> </html> `; Check your web app deployment settings too - make sure it’s set to execute as “User accessing the web app” instead of “Me”. Wrong permissions here cause blank screens. “Anyone with link” access is fine. Also double-check that your URL actually has a value. Debug by modifying your doGet function to return the URL as plain text first, then add the redirect once you know it’s working.