when new to Vue.js and to javascript. when trying to send some data to a google sheet without success.
there is found some info on how to post data, but my data is not stored in a form. So there is appended an empty ‘form’ with the data 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?uxxx", 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 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)
there is 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”}
Solution :
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.
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:
From:
var newRow = headers.map(function(header) {
return header === ‘timestamp’ ? new Date() : e.parameter[header]
})
To:
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 the 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 the actual Spreadsheet. If it is different from the above image, please modify the script for the situation.
References:
Web Apps
Taking advantage of Web Apps with Google Apps Script
If I misunderstood the question and this was not the result you want, I apologize.
Solution 2:
there is 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(‘’)