Send data in variable (not on form submit) to google sheet

0
votes

I am new to Vue.js and to javascript. I am trying to send some data to a google sheet without success.

I have found some info on how to post data, but my data is not stored in a form. So I have appended an empty 'form' with the data I want to post without luck.

This is the source: https://github.com/jamiewilson/form-to-google-sheets and: Is it possible to send js variables to google sheets? (Not form data)

This is how I post the data:

saveToGoogleSheet () {
  const scriptURL = 'https://script.google.com/macros/s/.../exec'
  const form = ''
  var sendingData = new FormData(form)
  sendingData.append('starRating', this.feedbackData.starRating)

  fetch(scriptURL, {method: 'POST', body: new FormData(sendingData)})
    .then(response => console.log('Success!', response))
    .catch(error => console.error('Error!', error.message))
}

this is the google script

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 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()
  }
}

After running my app, I receive a success message:

Success!
Response {type: "cors", url: "https://script.googleusercontent.com/macros/echo?u…xxx", redirected: true, status: 200, ok: true, …}
body: ReadableStream
bodyUsed: false
headers: Headers
__proto__: Headers
ok: true
redirected: true
status: 200
statusText: ""
type: "cors"
url: "https://script.googleusercontent.com/macros/echo?user_content_key=..."
__proto__: Response

but the google spreadsheet do not get populated, and yet the data exist if I console.log it.

1) What am I doing wrong?

2) Also, is there a way to send the data without appending an empty form?

3) Finally, what if I want to send more data, can I do something like

sendingData.append({
  'starRating' = this.feedbackData.starRating
  'nowReading' = this.feedbackData.nowReading
})

or do I need to do:

sendingData.append('starRating', this.feedbackData.starRating)
sendingData.append('nowReading', this.feedbackData.nowReading)

or what?

-- UPDATE --

after posting the question I decided to start afresh with Google Sheet. the result is that now I do post data to the sheet, but it is 'undefined' despite the data is there before and after the posting:

console.log(this.feedbackData)
this.saveToGoogleSheet()
console.log(this.feedbackData)

I have tried the single variable as per above, or posting all the variables at once (most of which were 'undefined' at the time of posting) like this:

saveToGoogleSheet () {
  const scriptURL = 'https://script.google.com/macros/s/.../exec'
  fetch(scriptURL, {method: 'POST', body: JSON.stringify(this.feedbackData)})
    .then(response => console.log('Success!', response))
    .catch(error => console.error('Error!', error.message))
}

and like this:

saveToGoogleSheet () {
  const scriptURL = 'https://script.google.com/macros/s/.../exec'
  fetch(scriptURL, {method: 'POST', body: this.feedbackData})
    .then(response => console.log('Success!', response))
    .catch(error => console.error('Error!', error.message))
}

but the result is the same.

My JSON.stringify(this.feedbackData) looks like this:

{"userName":"","userNumber":"","friendName":"","friendNumber":"","inviteMessage":"","nowReading":"S2/E1/","starRating":"4"}
2
javascriptvue.jsgoogle-apps-scriptgoogle-sheets
Hi, I'd suggest you to play with the api exporer : developers.google.com/apis-explorer/#p/sheets/v4 , try to append your values from here just to be sure your data is good ! – Loïc Monard
Hi, I don't understand how I should apply the info in the link, sorry. As you can read in the updated posting, I do post to Google Sheet, but the data is always 'undefined', even if I post 1 single piece of variable in the format required by fetch(). – Marcello
@Marcello I deeply apologize that my answer was not useful for your situation. – Tanaike

2 Answers

1
votes

I have found the problem:

in here:

saveToGoogleSheet () {
  const scriptURL = 'https://script.google.com/macros/s/.../exec'
  const form = ''
  var sendingData = new FormData(form)
  sendingData.append('starRating', this.feedbackData.starRating)

  fetch(scriptURL, {method: 'POST', body: new FormData(sendingData)})
    .then(response => console.log('Success!', response))
    .catch(error => console.error('Error!', error.message))
}

I should change this:

fetch(scriptURL, {method: 'POST', body: new FormData(sendingData)})

into this:

fetch(scriptURL, {method: 'POST', body: sendingData})

Also, I don't need const form = '' if I put var sendingData = new FormData('')

0
votes
  • You have already deployed Web Apps for accepting value.
  • You want to put the values to the Spreadsheet like the below image by sending the object of {"userName":"","userNumber":"","friendName":"","friendNumber":"","inviteMessage":"","nowReading":"S2/E1/","starRating":"4"} with fetch() of Javascript.

enter image description here

If my understanding is correct, how about this modification?

Modification points:

  • When the value is sent using POST method, the value can be retrieved with e.postData.contents from e of doPost(e).
    • But in this case, the value is required to be sent as a string. So please use JSON.parse().
    • So please use body: JSON.stringify(this.feedbackData)} at fetch() side.

Modified script:

Please modify as follows.

Google Apps Script:

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

var params = JSON.parse(e.postData.contents); // Added
var newRow = headers.map(function(header) {
  return header === 'timestamp' ? new Date() : params[header]; // Modified
});

Javascript:

Please use the above script of "UPDATE" section in your question.

saveToGoogleSheet () {
  const scriptURL = 'https://script.google.com/macros/s/.../exec'
  fetch(scriptURL, {method: 'POST', body: JSON.stringify(this.feedbackData)})
    .then(response => console.log('Success!', response))
    .catch(error => console.error('Error!', error.message))
}

Note:

  • When you modified the Google Apps Script of Web Apps, please deploy the Web Apps as new version. By this, the latest script is reflected to Web Apps. Please be careful this.
  • I'm not sure about your actual Spreadsheet. If it is different from the above image, please modify the script for your situation.

References:

If I misunderstood your question and this was not the result you want, I apologize.