Please Help. The Solution may benefit Wix Community at large

Ask the community
,
1

Hey Guys,

I have been trying this for 9 days now. I have read through 100’s of forum questions but cannot find a solution.

I want to send my Form Data (AfterSave) to Google Sheets

I created a Web App on Google Sheet which is something like this

function doGet(e){
  return handleResponse(e);
}
        var SHEET_NAME = "Sheet1";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); 

function doPost(e){
  return handleResponse(e);
}

function handleResponse(e) {
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  
  
  try {
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("1-L-rFuTM4oWbzcoLCfAoTGvvwC0iSiCdKtZRvl3RdRI"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 

    for (i in headers){
      if (headers[i] == "Timestamp"){ 
        row.push(new Date());
      } else { 
        row.push(e.parameter[headers[i]]);
      }
    }

    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    
    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();
  }
}

function setup() {
    var doc = SpreadsheetApp.openById('1-L-rFuTM4oWbzcoLCfAoTGvvwC0iSiCdKtZRvl3RdRI');
    SCRIPT_PROP.setProperty("key", doc.getId());
}

Then I went ahead and created a Backend .JSW on my Wix Site which looks like this

//sendSheets.jsw

import {fetch} from 'wix-fetch';

export function sendToSheets(data) {	
	fetch("https://script.google.com/macros/s/AKfycbykq05IP_ABzQfLa9ura_pISv-zwUHZdeGb2F9681ffyzubXi9I/exec", {
		method: 'post',
		body: data
	});
}

Finally on my Page Code it looks like this

import {sendToSheets} from 'backend/sendSheets.jsw'; 

$w.onReady(function () {
  $w("#dataset1").onAfterSave(sendToSheets);
});

export function sendFormData() {
	
	var data = {
		"input1":$w("#input1").value,
		"input2":$w("#input2").value,
		"dropdown1":$w("#dropdown1").value,
		"dropdown2":$w("#dropdown2").value
	};
		
	sendToSheets(data);
	
}

In the end the Script I created on Google Script Editor is showing that my Script ran a total of 7 times which is the exact amount of times I submitted a Form but nothing is posted on my sheets

1366×768 102 KB

I come from a Marketing Background and I only know a little bit of coding (I’m trying to learn) but I really cannot understand what to do.

If anyone can help I think it will be a great help to the Wix Community as a whole because this is something many people can implement.