Insert to specific Sheet Name based on form input data

Viewed 175

I wanted to insert my data to a specific sheet name based on form input value of "svdate":

<form method="get" action="submission-success.php" name="google-sheet">

        <div class="form-group">Customer Name:
            <input type="text" name="Customer" class="form-control" value="Michael Wilson" required readonly />
        </div>
        <div class="form-group">Total:
            <input type="text" name="Amount" class="form-control" value="$120.00" required readonly />
        </div>
        <div class="form-group">Cleaner Name:
            <input type="text" name="Cleaners" class="form-control" value="Dee" required readonly />
        </div>
        

        <div>
            <input type="text" name="svdate" class="form-control" value="06/17/2021" required readonly hidden />
        </div>

     <! –– some other fields here -->

        <div class="form-group">
            <button type="" id="qid" class="button" onclick="this.classList.toggle('button--loading')">
                <span class="button__text">Submit</span>
            </button> 
        </div>
</form>

Here are our sheet names which are all based on dates:

enter image description here

Since the svdate on our form is 06/17/2021, the form data should be inserted to 6/17 sheetname. How can I do it?

Here's my script in Code.gs to insert data but I don't know how to assign the value to my sheetName variable:

var sheetName = // I want to assign the "svdate" value here...

    var scriptProp = PropertiesService.getScriptProperties()

    function intialSetup () {
      var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
      scriptProp.setProperty('key', activeSpreadsheet.getId())
    }

    function doPost (e) {
      var lock = LockService.getScriptLock()
      lock.tryLock(10000)

      try {
        var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
        var sheet = doc.getSheetByName(sheetName)

        var headers = sheet.getRange(1, 1, 1, 10).getValues()[0]
        var nextRow = sheet.getLastRow() + 1

        var newRow = headers.map(function(header) {
          return header === 'timestamp' ? new Date() : e.parameter[header]
        })
        
        var find = sheet.getRange(1, 1, sheet.getLastRow()).createTextFinder(newRow[0]).matchEntireCell(true).findNext(); // Added
        sheet.getRange(find ? find.getRow() : nextRow, 1, 1, newRow.length).setValues([newRow]); // Added
        
        return ContentService
        .createTextOutput(e.parameter.callback + "(" + JSON.stringify({ 'result': 'success', 'row': nextRow })+ ")")
        .setMimeType(ContentService.MimeType.JAVASCRIPT);
      }

      catch (e) {
        return ContentService
          .createTextOutput(e.parameter.callback + "(" + JSON.stringify({ 'result': 'error', 'error': e })+ ")")
          .setMimeType(ContentService.MimeType.JAVASCRIPT)
      }

      finally {
        lock.releaseLock()
      }
    }
1 Answers

I thought that in your situation, it is required to retrieve 6/17 from 06/17/2021. For this, how about the following modification?

Modified script:

In this case, please modify doPost as follows.

From:

try {
  var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
  var sheet = doc.getSheetByName(sheetName)

To:

try {
  sheetName = e.parameter.svdate.split("/").splice(0, 2).map(e => e.replace(/^0/, "")).join("/");
  var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
  var sheet = doc.getSheetByName(sheetName);
  if (!sheet) throw new Error(`${sheetName} was not found.`);
  • In this modified script, when the sheet name of sheetName is not found in the Spreadsheet, an error ${sheetName} was not found. is returned.

Note:

  • When you modified the Google Apps Script, please modify the deployment as new version. By this, the modified script is reflected to Web Apps. Please be careful this.
  • You can see the detail of this at the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
  • This answer supposes that your current script works fine. Please be careful this.

References:

Related