1

When I run this script it show me this error. I can't understand what I do. Its function is to transfer results from html form to Google Sheet. Can anyone solve this problem?

var sheetName = 'Sheet1'
        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, sheet.getLastColumn()).getValues()[0]
            var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues().map(function(row){
            return row[0];
            });
            var nextRow = sheet.getLastRow() + 1

            var newRow = headers.map(function(header) {
              return header === 'timestamp' ? 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()
          }
        }

chek this image

Improve this question

1 Answer 1

Reset to default
0

SpreadsheetApp.getActiveSpreadsheet() is returning null. This happens when the script project is not bounded to a spreadsheet and when the bounded project is opened by using it's URL or from the Google Apps Script hub (https://script.google.com) instead of opening from the container.

There are two possible solutions,

  1. use your script in bounded project
  2. replace getActiveSpreadsheet() by open(file), openById(id) or openByUrl(url) (you should have to pass an appropriate object / value

Resources

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.