Split Incoming Data in Into Two Different Sheets Using Apps Script and Google Sheets-CodePudding

I need some help with Apps Script and Google Sheets. I don't know much about coding and programming, but I'm trying to tweak a script. I have a website that sends variables to Apps Script and then posted in Google Sheets. But I want these variables in two separate groups. Let's call them Data 1 and Data 2. Data 1 goes in Sheet 1, and Data 2 goes in Sheet 2. Currently, both data are sent and processed under one function, meaning Sheet 1 and Sheet 2 are populated at the same time. Also both data include a timestamp per row. However, if there is no Data 2, Sheet 2 is still being populated with timestamps. But I only want either Sheet 1 or Sheet 2 (no timestamps or anything else in the other sheet) depending on what the website sends.

I tried to split the script into two functions, but only one ended up working, perhaps because I'm using the same function name? I tried to rename the second function, but it didn't work either, possibly because I may be breaking naming conventions or the way App Script doesn't allow two postData per script? If empty variables are not a valid factor for Apps Script to stop populating the other sheet, do I need to use conditionals, and if yes, how? I'm not sure where to go. Could you help me with this? Here's a script:

function doPost(postData) {
  let doc = SpreadsheetApp.openById(MY_SHEET_ID);
  let sheet = doc.getSheetByName("Sheet 1");
  let sheet2 = doc.getSheetByName("Sheet 2");
  let parsedData = postData.parameter;
  parsedData = JSON.parse(Object.keys(parsedData));

  let userBlue = parsedData["Blue"]
  let userPurple = parsedData["Purple"]
  let userRed = parsedData["Red"]
  let userOrange = parsedData["Orange"]
  
  sheet.appendRow([new Date(), userBlue, userPurple]);
  sheet2.appendRow([new Date(), userRed, userOrange]);  return ContentService.createTextOutput("Success");
    }

Thank you in advance

CodePudding user response：

Although I am not able to fully reproduce using a website I believe a conditional statement would work here:

function myFunction() {

  const MY_SHEET_ID = "1m9nLp5sgpignwB1ddddxxxxxxxxx8";
  let doc = SpreadsheetApp.openById(MY_SHEET_ID);

  let sheet = doc.getSheetByName("Sheet1");
  let sheet2 = doc.getSheetByName("Sheet2");

  var myOB = { "Blue": 'BlueBerries', "Purple": 'grapes', "Red": 'apple', "Orange": '' }

  if(myOB.Blue != "" && myOB.Purple != ""){ //Use && if you want to make sure no data is posted if both variables are empty
    sheet.appendRow([new Date(),myOB.Blue,myOB.Purple])
  }if(myOB.Red != "" || myOB.Orange != ""){  //Use && if you want to make sure no data is posted if both variables are empty
    sheet2.appendRow([new Date(),myOB.Red,myOB.Orange])

  }else{
    ContentService.createTextOutput("DataSet1 Empty - No changes made");
  }
}

So in your case, you can try this:

  if (userBlue != "" && userPurple != "") {
    sheet.appendRow([new Date(), userBlue, userPurple]);
    ContentService.createTextOutput("Success");
  }
  else {
    return ContentService.createTextOutput("Failed");
  }
  if (userRed != "" && userOrange != "") {
    sheet2.appendRow([new Date(), userRed, userOrange]);
    ContentService.createTextOutput("Success");
  } else {
    return ContentService.createTextOutput("Failed");
  }

In the above, variables will be evaluated if they are not empty then data will be added to its respective Sheet.

Using if-else

Using if-else