1

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

MStar
  • 13
  • 2
  • I have to apologize for my poor English skill. Unfortunately, from your question and your showing script, I cannot understand `but only one ended up working, perhaps because I'm using the same function name?`. Can I ask you about the detail of your current situation? – Tanaike Apr 06 '22 at 09:01
  • Hi, Tanaike. This sentence: "but only one ended up working, perhaps because I'm using the same function name?" is about a different script you don't see here. I was talking about my efforts and attempts to solve the problem. One of my attempts was splitting my function in two different ones. Only one of the functions worked and the other one didn't work. However, no need to focus on the second paragraph. My second paragraph is about unsuccessful attempts I tried to use. My first paragraph is what I want to do. If reading the first paragraph only still sounds confusing, I can rephrase it. – MStar Apr 06 '22 at 09:16
  • Thank you for replying. From your replying, I understood that the function you want to use is not shown in your question. From this situation, for example, when you have already confirmed that 2 functions you want to use work fine as the independent function, how about executing them like `function1(); function2();`? But, I cannot know your actual script. So if this method was not useful, I apologize. – Tanaike Apr 06 '22 at 09:22
  • The script on my original post is what I want to fix. So you are saying that function doPost(postData) { } and function2 doPost(postData) { } will actually work within the same script? Or how can I trigger a function just for sheet.appendRow(), and another one just for sheet2.appendRow() ? – MStar Apr 06 '22 at 11:24
  • If `function doPost(postData) { } and function2 doPost(postData) { }` is the situation that 2 functions of `function doPost(postData) { }` are existing. And, when you want to merge this, I thought that this answer might be useful. https://stackoverflow.com/a/62603739 – Tanaike Apr 06 '22 at 11:35
  • Is this what I should do then? `function doPost1 (postData) { }` and `function doPost2 (postData) { }` – MStar Apr 06 '22 at 12:37
  • Thank you for replying. About `Is this what I should do then? function doPost1 (postData) { } and function doPost2 (postData) { }`, unfortunately, I'm not sure about your actual script from your question. So, that sample answer is just my guess. This is due to my poor English skill. I deeply apologize for this. I would be grateful if you can forgive my poor English skill. – Tanaike Apr 06 '22 at 12:53
  • That's okay. Thank you, for trying Tanaike. I appreciate it. – MStar Apr 06 '22 at 13:17
  • Hey MStar, good question but can you include a snapshot of your origin dataset, desired outcome and current outcome? Wanna see the code to outcome correlation and see how we can tweek your code. Thx – Nami888 Apr 06 '22 at 16:53
  • Could you please share the entire code you are looking to fix? How are you capturing both variables? also a sheet where you have some data so we can see a better sample? – Yancy Godoy Apr 07 '22 at 01:15
  • Hi, @LucasLui888 and @Yancy. The code I posted in my original post, plus this `const MY_SHEET_ID = "XXXXX";` is the entire code. On my website, I have this: ``dataFromWebsite({"Blue": $blue, "Purple": $purple, "Red": $red, "Orange": $orange})`` That code sends my variables: Blue, Purple, Red, and Orange to Apps Script. Blue and Purple data go on Sheet 1 and Red and Orange on Sheet 2. Timestamps for both. But I don't want timestamps when either Blue and Purple or Red and Orange are empty. See the screenshot in [the link here for further clarification](https://ibb.co/K6JzPNM) – MStar Apr 07 '22 at 10:17
  • Hey @MStar thanks for the screenshot! Very clear on the issue. – Nami888 Apr 08 '22 at 01:21

1 Answers1

1

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.

Yancy Godoy
  • 582
  • 2
  • 13
  • Thank you so much. It works, but I couldn't figure out why it would only work with either the first "If" or the second "If." I tweaked your script and got this `if (userBlue != "" && userPurple != "") { sheet.appendRow([new Date(), userBlue, userPurple]); } else if (userRed != "" && userOrange != "") {sheet2.appendRow([new Date(), userRed, userOrange]); ContentService.createTextOutput("Success"); } else { return ContentService.createTextOutput("Failed"); } ` It works so far, but not sure how stable it is. Do you have any thoughts on the edits I made? – MStar Apr 09 '22 at 10:18
  • Just to add to my previous comment. When my website sends data to Apps Script using your script, it processed one of the conditionals, for example **userBlue** and **userPurple**, but later on, when a trigger in the website sent **userRed** and **userOrange** nothing would happen, only until **userBlue** and **userPurple** data would come again. With the edits I made, now it can alternate between both options. It's working, but it looks strange to have `ContentService.createTextOutput("Success")` in only one of the conditionals. Is that going to cause any issues? – MStar Apr 09 '22 at 10:29
  • No, it won't because ```ContentService.createTextOutput("Success")``` is for you to know whether that task was successful or not. As long as it works as you need to, it won't affect. – Yancy Godoy Apr 11 '22 at 16:12
  • I'm marking this question as solved with Yancy Godoy's script. However, if you don't want your script to stop after one of the IF functions and keep working, try removing the first linke that has `ContentService.createTextOutput("Success")` although I'm not sure how this affects the rest of the process. So far, it has been working for me. – MStar Apr 12 '22 at 09:46