Send URL Parameters to Google Sheet on Page Load

I am looking for a way to Send URL Parameters to a Google Sheet as soon as the page is loaded,

If I have a URL like, https://example.com/test?param1=value&param2=another

How could I get the data "value" and "another" added to a google sheet on load?

I am currently using the Form to Google Sheets Demo as published on GitHub here:

<https://github.com/jamiewilson/form-to-google-sheets>

My current code is prefilling the text inputs, but it requires the user to press submit, instead I would like it to all happen without the need for any action from the user.

How would I do that?

I have explored many ways of auto-submitting forms on page load, but none of these have worked.

However is that the best path to go down?

Below is my code, minus CSS.

My HTML

<form name="submit-to-google-sheet">
    <input type="text" id="inputBoxParam1" name="Param1" placeholder="Param1"/>
    <br><br>
    <input type="text" id="inputBoxParam2" name="Param2" placeholder="Param2" />
    <br><br>
    <button type="submit">Confirm Details</button>
</form>

My JavaScript

<script type="text/javascript">

// Prefill the input boxes from parameter values
function getURLParameter(e) {
  return decodeURI((new RegExp(e + "=(.+?)(&|$)").exec(location.search) || [, ""])[1]);
}

if (getURLParameter("Param1").includes("@")) {
  document.getElementById("inputBoxParam1").value = getURLParameter("Param1");
} 
else {
  console.log("No Param1 parameter found.");
}

if (getURLParameter("Param2")) {
  document.getElementById("inputBoxParam2").value = getURLParameter("Param2");
} 
else {
  console.log("No Param2 parameter found.");
}


// Code From GitHub
const scriptURL = 'https://script.google.com/macros/s/examplescript'
const form = document.forms['submit-to-google-sheet']

form.addEventListener("submit", e => {
e.preventDefault()
fetch(scriptURL, { method: 'POST', body: new FormData(form)})
  .then(response => console.log('Success!', response))
  .catch(error => console.error('Error!', error.message))
})

</script>

I'm only a 17 year old beginner so any help would be appreciated

I believe your goal is as follows.

• You want to automatically submit the values of the query parameter to your Web Apps when you open the HTML with the URL including the query parameter.

Modification points:

• I think that if you want to send the query parameter of value and another as the keys param1 and param2, https://example.com/test?param1=value¶m2=another is required to be modified to https://example.com/test?param1=value&param2=another. And, in your javascript, it seems that param1 is required to include @. But, in your query parameter, param1 is value. Please be careful about this.

• In your script, it seems that it is required to click submit button. In order to automatically submit the values using the query parameter, the button is not required to be used.

• In your quetion, Google Apps Script is not included. In this answer, I prepare a sample Google Apps Script for achieving your goal.

Modified script:

Please set your Web Apps URL to scriptURL. In this modification, the button was removed.

HTML & Javascript side:

<form name="submit-to-google-sheet">
    <input type="text" id="inputBoxParam1" name="Param1" placeholder="Param1"/>
    <br><br>
    <input type="text" id="inputBoxParam2" name="Param2" placeholder="Param2" />
    <br><br>
</form>

<script>
const queryParameters = Object.fromEntries(new URLSearchParams(window.location.search));

if (queryParameters["param1"].includes("@")) {
  document.getElementById("inputBoxParam1").value = queryParameters["param1"];
} else {
  console.log("No Param1 parameter found.");
}

if (queryParameters["param2"]) {
  document.getElementById("inputBoxParam2").value = queryParameters["param2"];
} else {
  console.log("No Param2 parameter found.");
}

const scriptURL = 'https://script.google.com/macros/s/###/exec'; // Please set your Web Apps URL.

const data = new FormData();
Object.entries(queryParameters).forEach(e => data.append(...e));
fetch(scriptURL, { method: 'POST', body: data})
  .then(response => console.log('Success!', response))
  .catch(error => console.error('Error!', error.message))
</script>

Google Apps Script side:

In this case, it supposes that the Google Apps Script project is the container-bound script of Google Spreadsheet. Please be careful about this. If your Google Apps Script project is the standalone type, please modify getActiveSpreadsheet() to openById("###spreadsheetId###").

const doPost = e => {
  const sheetName = "Sheet1"; // Please set your sheet name.

  const { param1, param2 } = e.parameter;
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.appendRow([param1, param2]); // Or,if you want to add the date to the 1st column, please use sheet.appendRow([new Date(), param1, param2])
  return ContentService.createTextOutput("ok");
}

Testing:

In this case, please access your URL of HTML like https://example.com/test?param1=value@value&param2=another. By this, the values of value@value and another are appended to the Spreadsheet.

Note:

• When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.

• You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".

• If you are not required to show the input tags in the HTML, you can use the following simple HTML.

  <script>
  const scriptURL = 'https://script.google.com/macros/s/###/exec';
  
  const queryParameters = [...new URLSearchParams(window.location.search).entries()];
  const data = new FormData();
  queryParameters.forEach(e => data.append(...e));
  fetch(scriptURL, { method: 'POST', body: data})
    .then(response => console.log('Success!', response))
    .catch(error => console.error('Error!', error.message))
  </script>