Google Apps Script sorting data in Google Sheets using an array or a .JSON File in a daily & trip basis

Question

The program basically collects information through Appsheet and stores its data into a sheet, BUS PASSENGER DETAILS SHEET, on application submit. Then, using the function below, It sorts it out on a table based on the Trip Number on a daily basis.

There are 2 more Functions the same as sortGender() that sorts out data depending if a individual is a Senior Citizen or a PWD (Person with Disabilities) but we will leave that out and focus on one sheet.

I am seeking insight with my code regarding the array I made for me to use to redirect data to where I need it to be. An example of this array is this

const sheetRanges = {
    "1": {male: "C12", female: "D12"},
    "2": {male: "E12", female: "F12"},
    "3": {male: "G12", female: "H12"},
    "4": {male: "I12", female: "J12"},
    "5": {male: "K12", female: "L12"},
    "6": {male: "M12", female: "N12"},
    "7": {male: "O12", female: "P12"},
    "8": {male: "Q12", female: "R12"},
    "9": {male: "S12", female: "T12"},
    "10": {male: "U12", female: "V12"},
    "11": {male: "W12", female: "X12"},
    "12": {male: "Y12", female: "Z12"},
    "13": {male: "AA12", female: "AB12"},
    "14": {male: "AC12", female: "AD12"},
    "15": {male: "AE12", female: "AF12"}
  };

this is then applied to

function sortGender(callback){
  var range = sheetNameDAVAO1.getRange(lastrow_davao1, 1, 1, 5);
  var value = range.getValues();
  Logger.log(value);

  value.forEach(x => {
    var currentTripNumber = values[1];
    var currentGender = values[3];
    if (currentTripNumber in sheetRanges && currentGender == "Male") {
      var maleRange = sheetNameOverall.getRange(sheetRanges[currentTripNumber].male);
      Logger.log(currentTripNumber);
      var addMale = maleRange.getValue() + 1;
      maleRange.setValue(parseInt(addMale));
      sheetNameMonthly.getRange("X10").setValue(parseInt(addMale));
      sheetNameOverall.getRange("J8").setValue(parseInt(addMale));

    } else if(currentTripNumber in sheetRanges && currentGender == "Female") {
      var femaleRange = sheetNameOverall.getRange(sheetRanges[currentTripNumber].female);
      var addFemale = femaleRange.getValue() + 1;
      femaleRange.setValue(parseInt(addFemale));
      sheetNameMonthly.getRange("X11").setValue(parseInt(addFemale))
      sheetNameOverall.getRange("J8").setValue(parseInt(addFemale));
    }
  });
  callback()
}

The sheet ranges from C12:AF12 and that is the first line of 31 rows which means the list goes on from C12:AF42. C12:AF12 runs for day 1, then C13:AF13 for day 2, and so on which gives me the task to efficiently store an array as big as this.

How can I approach this in a way that it efficiently gives me the arrays I need from day 1 to 31, from C12:AF12 to C42:AF42 daily until the end of the month?

The code above perfectly works and sorts out the data where I need it to be but sadly it only does until the first line and I don't have anything to cover all of the remaining cells below (C13:AF42).

I was thinking of creating a JSON file to store it just like the array I mentioned above from C12:AF12 for day 1 to C42:AF42 for day 31 but haven't got a clue on as to how to structure it in the JSON File and to utilize it in Google Apps Script. I have tried yes but not at this scale.

Sample of the link with code on Apps Script: https://docs.google.com/spreadsheets/d/19rluTp6-tQmiA13mTOF4KmK2ED5NP1WCSjWdZYBj1pE/edit?usp=sharing

so you just need to pull the data from Bus Passenger Details and place it (sorted, by day) into monthly summary? — MattKing, Jul 25 '23 at 11:23
In your sample images, row 2 of `24/07/2023 13:05:09` is put into "E12" of row 12 of "BUS PASSENGER DETAILS SHEET". From your logic, I thought that this row might be put into row 35 of "BUS PASSENGER DETAILS SHEET". But, you say `The code above perfectly works`. So, I cannot understand the logic for obtaining your expected result. I apologize for this. So, can I ask you about the detailed logic? First, I would like to correctly understand your question. — Tanaike, Jul 25 '23 at 12:05
@MattKing basically yeah. i just described it the best way i could. whats the best way to tackle this? i was thinking of storing the said arrays into a JSON file and call them from there but i havent gotten a clue where to start on that matter.i dont even know if its possible or not through that method. any insight will be appreciated! — Dean, Jul 25 '23 at 12:33
The best way is for you to try to do it yourself several times and decide for yourself which is the best for you — Cooper, Jul 25 '23 at 14:38
@Dean what is supposed to happen if someone chooses a slot that is already taken? — MattKing, Jul 25 '23 at 17:20
@MattKing a cell will be incremented by 1 everytime the trip number, day and gender matches the array and sorts it out depending on what cell the data is linked to. On the application, The trip number can be triggered to go from trip 1 to trip 15. so for example if in trip 1, 18 passengers are recorded from the application, it will be sorted out it C12:D12 for both male and female and so on the next trip, trip number 2, it will now be placed in E12:F12 so on. and on the next day, it will now be in C13:D13 so on. does this make any sense? — Dean, Jul 26 '23 at 00:41
@Cooper i totally agree! which is why until now I have done and am doing my fair share of trials and errors and it has been at least 2 weeks now. which leaves me to ask questions from people with expertise such as yourselves. currently I am still reading and searching for methods I may utilize while having this thread. good day! — Dean, Jul 26 '23 at 00:43
@Tanaike Good day sir! The Bus Passenger Details Sheet actually collects data from an application I created using Appsheet. This data consists of Date & Time, Trip Number, Age, Gender and PWD (Person with Disabilities). Trip Number can be changed from the application itself from Trip Number 1 to 15. Age, Gender and PWD will be used to determine if a Passenger is a Senior Citizen, a PWD, and to sort out their genders. Basically my problem is how to create a big enough array or a method I do not know about to sort out the details into the linked cells just like the sheetRanges array above.Thanks — Dean, Jul 26 '23 at 01:01
Thank you for replying. I have to apologize for my poor English skill. Unfortunately, from your reply, my questions were not resolved. But, I would like to try to understand your question. When I could correctly understand your logic for achieving your goal, I would like to think of a solution. — Tanaike, Jul 26 '23 at 05:15
@Tanaike no worries. I have placed a sample sheet with code on the link. basically the app collects data from bus passengers and stores it in a google sheet. The data then is sorted out into a sheet where we will get to see how many male and female passengers there are in one trip. The bus conductor then changes the Trip Number at each bus stop. this goes on until the bus is finished with its route. When the day has ended the next trips for tomorrow will now then be entered at the row below, until the end of the month. — Dean, Jul 26 '23 at 05:31
Thank you for replying. I have to apologize for my poor English skill, again. Unfortunately, from your reply, I cannot still understand your logic. In order to correctly understand it, can you provide the sample input and output situations you expect? By this, I would like to try to understand your logic. I would like to think of a solution by correctly understanding your question. — Tanaike, Jul 26 '23 at 05:33
@Tanaike i would love to send you screenshots of a step by step of whats happening. — Dean, Jul 26 '23 at 05:54
Thank you for replying. For example, can you add them to your provided Spreadsheet? Or, can you provide 2 sample Spreadsheets of the sample input and sample output situation? — Tanaike, Jul 26 '23 at 05:58
Data that is collected from Appsheet is moved to Bus Passenger Details Sheet. Here the data is sorted out into the table seen above. For example in row 2 there is data that was collected from the application which is, 24/07/2023 13:05:09 | 2 | 61 | Male | Yes since Trip Number is Set to 2 then E12 will be incremented by 1 since he is a Male Passenger on Trip Number 2 on this date. next Client would then be 24/07/2023 13:10:05 | 4 | 59 | Female| Yes Then the next data will be placed on J12 since it is a Female from Trip Number 4. my problem is the sorting of data for the next day. — Dean, Jul 26 '23 at 06:00
@Tanaike I have updated the pictures posted on the thread to simulate how the process should be and how the data should be sorted out. What i mean about the code working perfectly fine is that it only gets to fill out the first row because of sheetRanges making it impossible for me to fill in the 2nd row ( C13:AF13 ). which is why i need something to let me fill it out until the last row (C42:AF42) — Dean, Jul 26 '23 at 06:07
Thank you for replying. In your question, you say `How would you approach this in a way that it efficiently gives me the arrays I need from day 1 to 31, from C12:AF12 to C42:AF42 daily until the end of the month is my main concern for this.`. When I saw your sample images, "26/07/2023" is put into row 12 of "Overall" sheet. From this situation, in your logic, you want to put the rows of "BUS PASSENGER DETAILS SHEET" sheet in order without rearranging rows. Is my understanding correct? — Tanaike, Jul 26 '23 at 06:12
And, in your question, you say `There are 2 more Functions the same as sortGender() that sorts out data depending if a individual is a Senior Citizen or a PWD (Person with Disabilities) but we will leave that out and focus on one sheet.`. In this question, you want to use only 2 sheets of "BUS PASSENGER DETAILS SHEET" and "Overall". Is my understanding correct? — Tanaike, Jul 26 '23 at 06:15
that is totally correct. and this is because the 2 more functions/sheets has the same logic into it. so solving overall can also solve the 2 functions/sheets as well. the dates in overall sheet is there through a function setDate() which sets the date daily from Utilities.Formatdate — Dean, Jul 26 '23 at 06:16
Thank you for replying. From your reply, in this question, only 2 sheets of "BUS PASSENGER DETAILS SHEET" and "Overall" are used. Is my understanding correct? — Tanaike, Jul 26 '23 at 06:24
Thank you for replying. And, in your situation, for example, when the values have already been existing in "Overall", what do you want to do about the additional values from "BUS PASSENGER DETAILS SHEET" sheet? For example, when your showing image is the current situation, if a row of "26/07/2023" is added to "BUS PASSENGER DETAILS SHEET", what do you want to do in "Overall" sheet? Unfortunately, I cannot still understand your expected result. I apologize for my poor English skill again. — Tanaike, Jul 26 '23 at 06:25
@Tanaike actually, regarding the additional values from BUS PASSENGER DETAILS SHEET, i was planning to generate a copy of the sheet at the end of the day, clear content and reset before the next day starts. the sheet would then be renamed on a certain format for organizing purposes — Dean, Jul 26 '23 at 06:31
basically i would like it to place/sort out the data depending on which date it is on today and that inputs for the past day will remain there or will be cleared. — Dean, Jul 26 '23 at 06:32
Thank you for replying. In your situation, when the script is run, "Overall" sheet is always empty. Is my understanding correct? And, I cannot understand `basically i would like it to place/sort out the data depending on which date it is on today.`. — Tanaike, Jul 26 '23 at 06:33
Overall sheet should be filled out until the last day of the month, then will be resetted and generated a copy and make room for the next month. The dates can be used as a key for us to know which row the data should be sorted out into. — Dean, Jul 26 '23 at 06:35
Thank you for replying. Unfortunately, from your reply, I cannot still understand `basically i would like it to place/sort out the data depending on which date it is on today and that inputs for the past day will remain there or will be cleared.`. Unfortunately, I cannot still understand your expected result. I apologize for my poor English skill. — Tanaike, Jul 26 '23 at 06:50
the date in overall as you can see is initially placed there at the start of the day. the bus starts on trip 1 and increments the trip each bus stop until trip number 15. on each trip, the Form from appsheet that you can see above collects data of each passenger for the set trip number. Each time a passenger is listed, the data then sorts out in the overall, by gender, from trip 1 to trip 15. by then on the next day, a new date will be placed below the past process. then it continues until the last part of the table. does this let you understand it better? — Dean, Jul 26 '23 at 07:01
Can I ask you about the relationship between your showing sample images and `basically i would like it to place/sort out the data depending on which date it is on today and that inputs for the past day will remain there or will be cleared.`? I thought that your goal will be to achieve your sample images. But, from your reply, I'm worried that my understanding was not correct. — Tanaike, Jul 26 '23 at 07:03
nevermind that line as you already had the understanding correct initially. i just wanted to let you know that the date will be placed there before the process starts and that you could use that but as long as it sorts out properly and that it is beside the date is all that matters. — Dean, Jul 26 '23 at 07:04
@Tanaike ive updated the post for you to see what the appsheet UI looks like. — Dean, Jul 26 '23 at 07:09
Thank you for replying. From your reply, I proposed a sample script as an answer. Please confirm it. If I misunderstood your question and that was not useful, I apologize. — Tanaike, Jul 26 '23 at 07:14
@Dean Thank you for replying. From your reply, I'm confusing understanding your actual expected result. But, I would like to support you. When I could correctly understand your question, I would like to think of a solution. So, in the current stage, I think that my answer didn't resolve your actual expected result. So, I have to delete my answer. I think that this is due to my very poor English skill. I really apologize for this. — Tanaike, Jul 26 '23 at 08:14

Google Apps Script sorting data in Google Sheets using an array or a .JSON File in a daily & trip basis

0 Answers0