I am a beginner with code script. Can you help me with my function please?
I have bot and he send data to google sheets, he send name, phone, date and method of communication. I need that google sheets write in column C date when was get the data from phone. I only now get the date, but in addition i need if - else. "If the column C is not empty send their date since last request", in addition i think I need to add method forEach and method so that the data is updated automatically when phone is received. For this I think need trigger "doGet(e)" from google documentation (spread sheet image) Data get from webhook Here is my code:
function getDate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var numbers = ss.getActiveSheet().getRange("B2:B1000")
let dateGoogle = new Date();
var rr = ss.getActiveSheet().getRange("C1:C1000").setValue(dateGoogle);
}
Just in case. If you're able to run the function getDate() and all you need is to make it to fill cells in C column only for rows that have filled cells in B column it can be done this way:
function getDate() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getActiveSheet().getData();
var data = range.getValues();
let dateGoogle = new Date();
data.forEach(x => x[2] = (x[2] == '' && x[1] != '') ? dateGoogle : x[2]);
range.setValues(data);
}
If you ask how to run the function getData() via doGet() I have no answer.
function doPost(e) {
Logger.log(e.postData.contents);
Logger.log(e.postData.type);
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet1");
let data = JSON.parse(e.postData.contents);
let row = [];
['name','phone','date','method'].forEach(k => row.push(data[k]));
Logger.log(JSON.stringify(row))
sh.appendRow(row);
}
Below function Simulate what I imagine the bot can do to send data. This one is sending the data as JSON.
function sendData(obj) {
const url = ScriptApp.getService().getUrl();
const params={"contentType":"application/json","payload":JSON.stringify(obj),"muteHttpExceptions":true,"method":"post","headers": {"Authorization": "Bearer " + ScriptApp.getOAuthToken()}};
UrlFetchApp.fetch(url,params);
}
function saveMyData() {
sendData({name:"name",phone:"phone1",date:"date1",method:"post"});
}
You will have to Deploy the doPost(e) as a webapp.