Question

0

560

Vistas

How can I read the contents of a form within a DoGet() function?

I am making a map painter programme, that takes in X, Y and colour data from user input and stores in a Google spreadsheet.

The data is sent to the spreadsheet as a form, following this solution. When the form is submitted, it is sent as a new row to the sheet called Global. However, I have three sheets, Global Local and Dungeon and based on variables contained in the form data being submitted, I want the doGet() function in my appsheets to send the new row to the correct sheet.

Here is the appScript DoGet():

  function doGet (e) {

  const lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))

    //++++++++++++++++++SEND TO GLOBAL++++++++++++++++++++++

    const Global = doc.getSheetByName("Global")
   

    const headers = Global.getRange(1, 1, 1, Global.getLastColumn()).getValues()[0]
    const nextRow = Global.getLastRow() + 1

    const newRow = headers.map(function(header) {
      //returns a new array, produced by applying a fucntion to every value in an array 
    return header === 'UniqueID' ? new Date() : e.parameter[header]
    })


    Global.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    //++++++++++++++++++++SEND TO LOCAL++++++++++++++++++++

  
  }

To clairfy, this code WORKS but I am struggling with how to tell it WHICH sheet (Global Local and Dungeon) to send the new row too. Right now, they are all going to Global. However if I have a field in my form that says either Global, Local or Dungeon, how can I have the appscript read this and act accordingly?

Conceptually, I need to refer to the part of the incoming form array that contains the name of the sheet, and then I can write the script to use that as the name of the sheet like this:

const sheetname = doc.getSheetByName("Global/Local/Dungeon")

about 4 years ago · Juan Pablo Isaza

2 Respuestas

Responde la pregunta

0

You can use google.script.run to trigger functions in your script and pass values. See this overhauled script.

Sample Form:

Script:

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index');
}

function processData(data) {
  const lock = LockService.getScriptLock();
  lock.tryLock(10000);
  try {
    const doc = SpreadsheetApp.openById(ScriptProperties.getProperty('key'));
    const sheet = doc.getSheetByName(data['sheet']);

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const nextRow = sheet.getLastRow() + 1;

    const newRow = headers.map(function(header) {
      return header === 'timestamp' ? new Date() : data[header];
    });

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);
  } catch(e) {    
  } finally {
    lock.releaseLock();
  }
}

Index.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form name="submit-to-google-sheet">
      <label for="sheet">Sheet:</label><br>
      <select id="sheet" name="sheet">
        <option value="Local">Local</option>
        <option value="Global">Global</option>
        <option value="Dungeon">Dungeon</option>
      </select>
      <input name="email" type="email" placeholder="Email" required>
      <input name="firstName" type="text" placeholder="First Name">
      <input name="lastName" type="text" placeholder="Last Name">
      <button type="submit" onclick="google.script.run.processData(this.form);">Send</button>
    </form> 
  </body>
</html>

Input:

Output:

about 4 years ago · Juan Pablo Isaza Denunciar

0

Responde la pregunta

Encuentra empleos remotos

Accepted Answer · 2022-07-01T01:20:17.889Z

Octavia, thankyou for the detailed proof. However, the answer to my question was this:

const sheet = doc.getSheetByName(e.parameter[sheetName])

I am able to refer to the incoming form data using the syntax e.parameter[<column header>]

0

560