Question Sending data from unity App to Google Sheets

Hello, anyone familiar with app scripts?

I'm sending data from unity app to google sheets form then to template, send to a new spreadsheet be converted into pdf with needed data and then send as email,

I'm having trouble with the sheets, I have the where what I want to send should not go pass row 86.

In the email I get all rows of the new spreadsheet which is sending 7 pages, I only need one page to send for this or one range A1:AX86, I've been trying multiple solutions, anyone have a take on this

Thanks,

Code (CSharp):

1. function sendPdfEmailWithLatestData() {
2.   // Constants
3.   const SPREADSHEET_ID = SpreadsheetApp.getActiveSpreadsheet().getId();
4.   const FORM_RESPONSE_SHEET_NAME = 'FormResponses';
5.   const PERIODONTAL_CHART_SHEET_NAME = 'Periodontal Chart';
6.   const EMAIL_SUBJECT = 'Periodontal Chart';
7.   const EMAIL_BODY = 'Attached is the Periodontal Chart for your review.';
8.  
9.  
10.  
11.  
12.   // Get the active spreadsheet and the sheets by their name
13.   const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
14.   const formResponseSheet = activeSpreadsheet.getSheetByName(FORM_RESPONSE_SHEET_NAME);
15.   const periodontalChartSheet = activeSpreadsheet.getSheetByName(PERIODONTAL_CHART_SHEET_NAME);
16.  
17.   // Get the range of all data in the FormResponses sheet, including the header row
18.   const formResponseDataRange = formResponseSheet.getDataRange();
19.   const numRows = formResponseDataRange.getNumRows();
20.   const numColumns = formResponseDataRange.getNumColumns();
21.   const values = formResponseDataRange.getValues();
22.  
23.   // Get the values from the latest row in the FormResponses sheet
24.   const headerRow = values[0]; // first row is the header row
25.   const latestRow = values[numRows - 1]; // last row is the latest row
26.   const patientLastName = latestRow[headerRow.indexOf('Last Name')];
27.   const patientFirstName = latestRow[headerRow.indexOf('First Name')];
28.   const patientDateOfBirth = latestRow[headerRow.indexOf('Date of Birth')];
29.   const appointmentDate = latestRow[headerRow.indexOf('Date')];
30.   const appointmentTime = latestRow[headerRow.indexOf('Time')];
31.   const appointmentVisit = latestRow[headerRow.indexOf('Visit')];
32.   const patientEmail = latestRow[headerRow.indexOf('Email')];
33.  
34.   // Update the values in the Periodontal Chart sheet
35.   const columnNames = ['E7:L7', 'Q7:X7', 'AC7:AJ7', 'AM7:AO7', 'AR7:AT7', 'AW7:AX7'];
36.   columnNames.forEach(function(columnName) {
37.   // Clear the contents of the cells in the specified column
38.   periodontalChartSheet.getRange(columnName).clearContent();
39. });
40.   const valuesToSet = [patientLastName, patientFirstName, patientDateOfBirth, appointmentDate, appointmentTime, appointmentVisit];
41.   columnNames.forEach((columnName, index) => {
42.     periodontalChartSheet.getRange(columnName).setValue(valuesToSet[index]);
43.   });
44.  
45.  
46.  
47.   // Convert the active spreadsheet to a PDF file and save it to Google Drive
48.   const pdf = DriveApp.createFile(activeSpreadsheet.getAs('application/pdf'));
49.  
50.   pdf.setName('Periodontal Chart.pdf');
51.  
52.   // Send the email with the PDF file attached
53.   //GmailApp.sendEmail(patientEmail, EMAIL_SUBJECT, EMAIL_BODY, {attachments: [pdf]});
54.  
55.   // Delete the PDF file from Google Drive
56.   pdf.setTrashed(true);
57.  
58.  
59.   console.log(`Latest row: ${latestRow}`);
60. copySheetAndSendEmail(SpreadsheetApp.getActiveSpreadsheet(), patientEmail, "Periodontal Chart", "Attached is the Periodontal Chart for your review.",PERIODONTAL_CHART_SHEET_NAME);
61.  
62. }
63.  
64. function copySheetAndSendEmail(sourceSpreadsheet, emailAddress, emailSubject, emailBody, PERIODONTAL_CHART_SHEET_NAME) {
65.  
66.  
67.   // Get the source sheet
68.   const periodontalChartSheet = sourceSpreadsheet.getSheetByName(PERIODONTAL_CHART_SHEET_NAME);
69.   // Create a new spreadsheet
70.   var newSpreadsheet = SpreadsheetApp.create("Periodontal Chart");
71.  
72.   // Copy the sheet to the new spreadsheet, including data and formatting
73.   periodontalChartSheet.copyTo(newSpreadsheet).setName("Periodontal Chart.pdf");
74.  
75.   // Get all sheets in the newSpreadsheet
76.   var sheets = newSpreadsheet.getSheets();
77.  
78.   // Loop through all sheets
79.   for (var i = 0; i < sheets.length; i++) {
80.     var sheet = sheets[i];
81.  
82.     // If the sheet is not the Periodontal Chart sheet, delete it
83.     if (sheet.getName() != "Periodontal Chart.pdf") {
84.       newSpreadsheet.deleteSheet(sheet);
85.     }
86.   }
87.  
88. var sheet = SpreadsheetApp.getActiveSheet();
89. for(var i=87;i<=sheet.getLastRow();i++){
90.   sheet.hideRow(i);
91. }
92.  
93.   // Convert the specified range of cells to a PDF file and save it to Google Drive
94.   const pdf2 = DriveApp.createFile(newSpreadsheet.getAs('application/pdf'));
95.  
96.  
97.   // Send the email with the PDF file attached
98.   GmailApp.sendEmail(emailAddress, emailSubject, emailBody, {attachments: [pdf2]});
99.  
100.  
101.   // Delete the new spreadsheet
102.   pdf2.setTrashed(true);
103. }
104.  
105.  
106.  

If I manually goto file download in sheets I will get the export options for the pdf, though I'm not sure how to fully implement via app scripts

 

The above doesn't look like valid C# Unity code.

It seems like your question would be better suited to Google Sheets scripting forums / support boards.

 

ok My first Line was,
"Hello, anyone familiar with app scripts?", I'm well aware buddy , It's java script via app script,

here is the valid C# code that corresponds to that script

Code (CSharp):

1. using System.Collections;
2. using System.Collections.Generic;
3. using UnityEngine;
4. using UnityEngine.UI;
5. using UnityEngine.Networking;
6. public class SendToSite : MonoBehaviour
7. {
8.     [SerializeField] string LNameEntry = "entry.279789169";
9.     [SerializeField] string FNameEntry = "entry.2109662468";
10.     [SerializeField] string DNameEntry = "entry.1991720549";
11.     [SerializeField] string DTNameEntry = "entry.1786338539";
12.     [SerializeField] string TNameEntry = "entry.388041489";
13.     [SerializeField] string VNameEntry = "entry.1561148125";
14.     [SerializeField] string EmailEntry = "entry.1981904453";
15.  
16.     [SerializeField] string LName = "";
17.     [SerializeField] string FName = "";
18.     [SerializeField] string DName = "";
19.     [SerializeField] string DTName = "";
20.     [SerializeField] string TName = "";
21.     [SerializeField] string VName = "";
22.     [SerializeField] string Email = "";
23.  
24.     [SerializeField] string BaseURL = "https://docs.google.com/forms/u/0/d/e/1FAIpQLSc-hz5OUf0PMW52hCV_LnKVWUfTYwi6gRj2Y7mkCQUG24AfBQ/formResponse";
25.  
26.  
27.  
28.  
29.  
30.     IEnumerator sendToSite(string LN, string FN, string DN, string DT, string TN,string VN, string EM)
31.     {
32.         WWWForm form = new WWWForm();
33.  
34.         form.AddField(LNameEntry, LN);
35.         form.AddField(FNameEntry , FN);
36.         form.AddField(DNameEntry , DN);
37.         form.AddField(DTNameEntry, DT);
38.         form.AddField(TNameEntry , TN);
39.         form.AddField(VNameEntry , VN);
40.         form.AddField(EmailEntry, EM);
41.      
42.         UnityWebRequest www = UnityWebRequest.Post(BaseURL, form);
43.         yield return www.SendWebRequest();
44.         Debug.Log("Done");
45.     }
46.  
47.  
48.     public void Send(string l,string f,string d,string dt,string t,string v,string em)
49.     {
50.  
51.         LName  = l;
52.         FName = f;
53.         DName = d;
54.         DTName = dt;
55.         TName = t;
56.         VName = v;
57.         Email = em;
58.  
59.  
60.  
61.         StartCoroutine(sendToSite(LName, FName, DName, DTName, TName, VName, Email));
62.        
63.     }
64.  
65.  
66.  
67.  
68.  
69. }
70.  

I asked in a community that I'm apart of, I'm quite sure there is someone here...

 

Also, thanks for your reply you've been there before if I'm not mistaken, much appreciated!

 

Fair enough! I just see enough people mis-using terminology that I suspected that was going on.

If I understand your issue, you're trying to only get a subsection of the sheet to print.

Looking at your line 90 in the top script... is it possible those "hides" are still keeping the full document size boundaries unchanged?

What about actually copying ONLY lines 1 to 87 to a new sheet (in the same doc) and going to that and printing to PDF? That seems like it might be an easy thing to test...

ALSO: when used interactively, isn't there a secondary "what pages do you want?" dialog that comes up? Can you supply additional arguments to the PDF function call, such as a page range?

 

Got It, added these lines of code just before pdf conversion in function 2....
-// need to delete all rows after 85

const lastRow = newSpreadsheet.getLastRow();
console.log("Last row with data: " + lastRow);
const newSpreadsheetPC = newSpreadsheet.getSheetByName("Periodontal Chart.pdf");
var maxRows = newSpreadsheetPC.getMaxRows();
newSpreadsheetPC.deleteRows(85, maxRows-lastRow+2); -

/* // Convert the specified range of cells to a PDF file and save it to Google Drive
const pdf2 = DriveApp.createFile(newSpreadsheet.getAs('application/pdf')); */

 

It feels like there should be some way to publicly record this thread somewhere that people looking for Google Sheets scripting advice would be likely to find it.

Thanks for coming back to fill it in! Your answer is consistent with my mental model of a Sheet.

 

Yes, here is the next task, oh and ofc there is code clean up (afterwards), I have roughly 1,050+ data points, each object holds about 22 to 23 variables, and there is at the most 32, so that's 23*32, 1000 + ....

I have a base class that holds the data all ints except for maybe 2 vars, so they all need to be converted ToString

I want to create a script for each object that inherits the class, allow the user to make edits in the app then send

Instead of 100+ vars , I need a optimized way to send data as a string, 32 strings , each holding the data of that object,

I have the form response with the 32 responses, I then need to add to the app script to take in the strings for each object, parse, then place in cell.

I have 32 rows and I want to link the range of each row to each string, the data for each cell will be placed in the order of the string

this is the string (1,3,3,3,3,3,3,3) for each object to number 16, at number 17 to32 it's (3,3,3,3,3,3,3,1) the 3 is for 3 cells..., so like get the cell name (B8D8,B9,C9,D9,B10,C10,D10,.....B15,C15,D15) the I need to take the string received from unity, parse or set to cells accordingly

 

Hi, I am interessted about the realtime update google sheets with unity part, could you help me and tell me what is the workflow you are working with? Thanks in advance

 

is everything going well?

 

Hello , thanks for your held interest, can you tell me more of what you mean or asking for?

 

Yes thanks for asking, I achieved and updated and added scripts, I just needed clarification from someone who knows dentistry, to tweak a few more things, I could also use some additional helper scripts but yea

I created a mobile app for examinations, created a custom chart in Google excel/sheets along with a list, use app script to parse data recieved from app examination to desired cells in chart, convert to pdf, then send to desired recipient....

May I ask where you interested in the project?

 

Nice but really it kinda is publicly recorded, ... right here.. I didn't post all or the rest of associated scripts, but yea... thanks again