Whenever i try to send data to google sheet from my google extension i get this problem

I want to type many lines in my textarea and recive them in different rows in the google sheet file is that possible ? my html code

 <form method="POST" action="my script url">
                                <label style="font-size:20px;color: green;">Auto </label>  
                                <textarea name="Auto" type="text" id="autolist"></textarea>                                                              
                                <button type="submit"  role="button" id="addauto">Add to Auto</button><br><br>                                                                                               
                            </form>

google script

const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()

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

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

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

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

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

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

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

CodePudding user response：

In your situation, how about the following modification?

From:

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

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

To:

const rows = headers.map(header => header === 'Date' ? [new Date()] : (e.parameter[header] ? e.parameter[header].split("\n") : []));
const max = Math.max(...rows.map(r => r.length));
const temp = rows.map(r => [...r, ...Array(max - r.length).fill(null)]);
const newRows = temp[0].map((_, c) => temp.map(r => r[c]));
if (newRows.length > 0) {
  sheet.getRange(nextRow, 1, newRows.length, newRows[0].length).setValues(newRows);
}

• In this modification, the values including \n are split as an array. And, the array is put into the Spreadsheet. By this, each line is put into each cell.

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)".

• By the way, from your Javascript, in this case, it supposes that your Web Apps is deployed as "Execute as: Me" and "Who has access to the app: Anyone". Please be careful about this.