GPAL - Generally Positive Automation Library v1.0
GPAL The Fluent Automation LIbrary
Loading...
Searching...
No Matches
GoogleSheets.cs
1// =============================================================================
2// GPAL - Generally Positive Automation Library
3// Copyright © 2026 Software Decisions, Inc. All rights reserved.
4//
5// This file is part of GPAL.
6// Licensed under the Business Source License 1.1
7//
8// Primary development, architecture, and vision by Michael B. Vederman,
9// CEO of Software Decisions, Inc., Texas.
10//
11// Internal development maintained privately.
12// Public releases appear on GitHub: https://github.com/SoftwareDecisionsInc/GPAL.
13//
14// See LICENSE for full terms, including Additional Use Grant.
15// =============================================================================
16
17using System;
18using System.Collections.Generic;
19using System.IO;
20using System.Reflection;
21using System.Linq;
22using System.Net.Http;
23using System.Text;
24using System.Text.Json;
25using DocumentFormat.OpenXml.Wordprocessing;
26using GenerallyPositive;
27using GenerallyPositive.Browser;
28using static GenerallyPositive.Enums;
29
30namespace GenerallyPositive
31{
32 public class GoogleSheetsConfig
33 {
34 public string SheetsApiBase { get; set; } = "https://sheets.googleapis.com";
35 public string ScriptsApiBase { get; set; } = "https://script.googleapis.com";
36 public string SpreadsheetEndPoint { get; set; } = "/v4/spreadsheets/";
37 public string ReadRangeEndpoint { get; set; } = "/v4/spreadsheets/{0}/values/{1}!{2}";
38 public string WriteRangeEndpoint { get; set; } = "/v4/spreadsheets/{0}/values/{1}!{2}";
39 public string AppendRangeEndpoint { get; set; } = "/v4/spreadsheets/{0}/values/{1}!{2}:append";
40 public string FormatRangeEndpoint { get; set; } = "/v4/spreadsheets/{0}:batchUpdate";
41 public string ScriptContentEndpoint { get; set; } = "/v1/projects/{0}/content";
42 public string TriggerEndpoint { get; set; } = "/v1/projects/{0}/triggers";
43 public string AuthorizationHeader { get; set; } = "Authorization";
44 public string ValueInputOption { get; set; } = "?valueInputOption=RAW";
45 public string ScriptFileType { get; set; } = "SERVER_JS";
46 public string TitleField { get; set; } = "title";
47 public string HiddenField { get; set; } = "hidden";
48 public string Bearer { get; set; } = "Bearer";
49 public string UserEnteredFormatField { get; set; } = "userEnteredFormat";
50 public bool UseAppsScript { get; set; } = true; // Default to Apps Script mode
51 public string WebAppUrl { get; set; } // Store the deployed web app URL
52 }
53
54 public class GoogleSheets : IGoogleSheets
55 {
56 internal string SpreadsheetId { get; private set; }
57 internal string SheetName { get; private set; }
58 internal string ReadRange { get; private set; }
59 internal string WriteRange { get; private set; }
60 internal IGPALGrid<string> ReadData { get; private set; }
61 internal FormatType? OurFormatType { get; private set; }
62 internal NumberFormatType? NumberFormatType { get; set; }
63 internal string NumberFormatPattern { get; set; }
64 internal object FormatValue { get; private set; }
65 internal int? InsertPosition { get; private set; }
66 internal string InsertSeparator { get; private set; }
67 internal TriggerScheduleType TriggerSchedule { get; private set; }
68 internal string AppsScriptProjectId { get; private set; }
69 internal string ScriptName { get; private set; }
70 internal string FunctionName { get; private set; }
71 internal ScriptAccessType ScriptAccess { get; private set; } = ScriptAccessType.ME; // Restrict access by default
72 internal ExecuteAsType ExecuteAs { get; private set; } = ExecuteAsType.USER_DEPLOYING;
73 internal string DeploymentDescription { get; private set; } = "Web app deployment for script";
74 internal int VersionNumber { get; private set; } = 1;
75 internal GoogleSheetsConfig googleSheetsConfig { get; private set; }
76 internal IRESTClient SheetsRESTClient { get; private set; }
77 internal IRESTClient ScriptsRESTClient { get; private set; }
78 internal ICredentials Credentials { get; private set; }
79 internal GoogleCloud _googleCloud { get; private set; }
80
81 internal GoogleSheets()
82 {
83 googleSheetsConfig = new GoogleSheetsConfig();
84 SheetsRESTClient = GPAL.RESTClient.WithAPIBase(googleSheetsConfig.SheetsApiBase).ToGPALObject();
85 ScriptsRESTClient = GPAL.RESTClient.WithAPIBase(googleSheetsConfig.ScriptsApiBase).ToGPALObject();
86 _googleCloud = null;
87 }
88
89 private void InitializeMode()
90 {
91 bool hasAccessToken = !string.IsNullOrEmpty(((Credentials)Credentials)?.AccessToken);
92
93 if (Credentials != null)
94 {
95 if (false == hasAccessToken)
96 {
97 Credentials.FetchAccessToken(out string accessToken);
98 }
99
100 if (false == string.IsNullOrEmpty(((Credentials)Credentials)?.AccessToken))
101 {
102 var testResponse = GPAL.RESTClient.WithAPIBase("https://oauth2.googleapis.com")
103 .WithEndpoint("/tokeninfo")
104 .WithParameters(new { access_token = ((Credentials)Credentials)?.AccessToken })
105 .Execute();
106 if (testResponse != null && !testResponse.Contains("error"))
107 {
108 googleSheetsConfig.UseAppsScript = false; // Switch to API mode
109 return;
110 }
111 }
112 }
113 googleSheetsConfig.UseAppsScript = true; // Fallback to Apps Script mode
114 }
115
116 private string ResolveSheetName(object sheetNameOrId)
117 {
118 if (sheetNameOrId == null)
119 {
120 return SheetName ?? "Sheet1";
121 }
122
123 if (sheetNameOrId is string name)
124 {
125 return string.IsNullOrEmpty(name) ? SheetName ?? "Sheet1" : name;
126 }
127 else if (sheetNameOrId is int id)
128 {
129 var spreadsheetResponseJson = SheetsRESTClient
130 .WithEndpoint($"{googleSheetsConfig.SpreadsheetEndPoint}{SpreadsheetId}")
131 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
132 .Execute();
133
134 if (spreadsheetResponseJson != null)
135 {
136 try
137 {
138 var spreadsheetResponse = JsonSerializer.Deserialize<SpreadsheetResponse>(spreadsheetResponseJson);
139 var targetSheet = spreadsheetResponse?.Sheets?.FirstOrDefault(s => s.Properties.SheetId == id);
140 if (targetSheet != null)
141 {
142 return targetSheet.Properties.Title;
143 }
144 }
145 catch { }
146 }
147
148 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Sheet with ID [{id}] not found", this, GPALObjectType.GoogleSheets);
149 return null;
150 }
151 else
152 {
153 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Invalid sheet name or ID type", this, GPALObjectType.GoogleSheets);
154 return null;
155 }
156 }
157
158 private object ResolveSheetIdOrName(object sheetNameOrId)
159 {
160 if (sheetNameOrId == null)
161 {
162 return SheetName ?? "Sheet1";
163 }
164
165 if (sheetNameOrId is string name)
166 {
167 return string.IsNullOrEmpty(name) ? SheetName ?? "Sheet1" : name;
168 }
169 else if (sheetNameOrId is int id)
170 {
171 return id;
172 }
173 else
174 {
175 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Invalid sheet name or ID type", this, GPALObjectType.GoogleSheets);
176 return null;
177 }
178 }
179
180 #region NEW
181 private bool ReadDataFromRange()
182 {
183 if (string.IsNullOrEmpty(SpreadsheetId))
184 {
185 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Spreadsheet ID not set", this, GPALObjectType.GoogleSheets);
186 return false;
187 }
188
189 if (string.IsNullOrEmpty(SheetName))
190 {
191 SheetName = "Sheet1";
192 GPAL.PublishSimpleEvent(GPALEventType.INFO, "Using default sheet name 'Sheet1'", this, GPALObjectType.GoogleSheets);
193 }
194
195 if (string.IsNullOrEmpty(ReadRange))
196 {
197 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Range not set for reading data", this, GPALObjectType.GoogleSheets);
198 return false;
199 }
200
201 if (googleSheetsConfig.UseAppsScript)
202 {
203 var response = CallWebApp("ReadRange", new { spreadsheetId = SpreadsheetId, sheetName = SheetName, range = ReadRange });
204 object successObj = null;
205 object dataObj = null;
206 if (response != null && response.TryGetValue("success", out successObj) && successObj is bool && (bool)successObj && response.TryGetValue("data", out dataObj))
207 {
208 try
209 {
210 var dataElement = JsonSerializer.Deserialize<JsonElement>(dataObj.ToString());
211 ReadData = dataElement.EnumerateArray()
212 .Select(row => row.EnumerateArray().Select(cell => cell.GetString()).ToList())
213 .ToList() as IGPALGrid<string>;
214 return true;
215 }
216 catch (Exception ex)
217 {
218 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to parse Apps Script read data", this, GPALObjectType.GoogleSheets, ex);
219 return false;
220 }
221 }
222 else
223 {
224 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to read range via Apps Script", this, GPALObjectType.GoogleSheets);
225 return false;
226 }
227 }
228 else
229 {
230 string endpoint = string.Format(googleSheetsConfig.ReadRangeEndpoint, SpreadsheetId, SheetName, ReadRange);
231 var responseJson = SheetsRESTClient
232 .WithEndpoint(endpoint)
233 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
234 .Execute();
235
236 if (responseJson != null)
237 {
238 try
239 {
240 var valueRange = JsonSerializer.Deserialize<ValueRange>(responseJson);
241 ReadData = valueRange.values?.Select(row => row.Select(cell => cell?.ToString()).ToList()).ToList() as IGPALGrid<string>;
242 return true;
243 }
244 catch (Exception ex)
245 {
246 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to parse API read data", this, GPALObjectType.GoogleSheets, ex);
247 return false;
248 }
249 }
250 else
251 {
252 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to read range via API", this, GPALObjectType.GoogleSheets);
253 return false;
254 }
255 }
256 }
257
258 private dynamic CallWebApp(string action, object parameters)
259 {
260 if (string.IsNullOrEmpty(googleSheetsConfig.WebAppUrl))
261 {
262 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Web app URL not set", this, GPALObjectType.GoogleSheets);
263 return null;
264 }
265
266 var payload = new { action, parameters };
267 var responseJson = SheetsRESTClient
268 .WithEndpoint(googleSheetsConfig.WebAppUrl)
269 .WithParameters(payload)
270 .WithHttpMethod("POST")
271 .WithHeader("Content-Type", "application/json")
272 .Execute();
273
274 if (responseJson == null)
275 {
276 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to call web app for action: [{action}]", this, GPALObjectType.GoogleSheets);
277 return null;
278 }
279
280 try
281 {
282 return JsonSerializer.Deserialize<Dictionary<string, object>>(responseJson);
283 }
284 catch (Exception ex)
285 {
286 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to parse web app response", this, GPALObjectType.GoogleSheets, ex);
287 return null;
288 }
289 }
290
291 public IAllowSheetSelection SaveTo(out IGPALGrid<string> gPalGrid)
292 {
293 if (string.IsNullOrEmpty(SpreadsheetId))
294 {
295 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Spreadsheet ID not set", this, GPALObjectType.GoogleSheets);
296 gPalGrid = null;
297 return this;
298 }
299
300 if (ReadData == null)
301 {
302 if (!string.IsNullOrEmpty(ReadRange))
303 {
304 if (!ReadDataFromRange())
305 {
306 gPalGrid = null;
307 return this;
308 }
309 }
310 else
311 {
312 GPAL.PublishSimpleEvent(GPALEventType.WARNING, "No data to save; ReadData is null and no range specified", this, GPALObjectType.GoogleSheets);
313 gPalGrid = null;
314 return this;
315 }
316 }
317
318 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Saving [{ReadData.Rows}] rows of sheet [{SheetName}] to grid.", this, GPALObjectType.GoogleSheets);
319
320 gPalGrid = ReadData;
321
322 return this;
323 }
324
325 public IAllowDataOperations AppendToCell(string cell)
326 {
327 if (string.IsNullOrEmpty(cell))
328 {
329 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Cell reference cannot be empty", this, GPALObjectType.GoogleSheets);
330 return this;
331 }
332
333 if (string.IsNullOrEmpty(SpreadsheetId))
334 {
335 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Spreadsheet ID not set", this, GPALObjectType.GoogleSheets);
336 return this;
337 }
338
339 if (string.IsNullOrEmpty(SheetName))
340 {
341 SheetName = "Sheet1";
342 GPAL.PublishSimpleEvent(GPALEventType.INFO, "Using default sheet name 'Sheet1'", this, GPALObjectType.GoogleSheets);
343 }
344
345 if (ReadData == null)
346 {
347 if (!string.IsNullOrEmpty(ReadRange))
348 {
349 if (!ReadDataFromRange())
350 {
351 return this;
352 }
353 }
354 else
355 {
356 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No data to append; ReadData is null and no range specified", this, GPALObjectType.GoogleSheets);
357 return this;
358 }
359 }
360
361 // Note: Flattening grid to string; assuming user intent for single cell operations
362 if (googleSheetsConfig.UseAppsScript)
363 {
364 var response = CallWebApp("AppendToCell", new { spreadsheetId = SpreadsheetId, sheetName = SheetName, cell, data = ReadData, separator = InsertSeparator });
365 object successObj = null;
366 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
367 {
368 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to append to cell via Apps Script", this, GPALObjectType.GoogleSheets);
369 }
370 }
371 else
372 {
373 string endpoint = string.Format(googleSheetsConfig.ReadRangeEndpoint, SpreadsheetId, SheetName, cell);
374 var readResponseJson = SheetsRESTClient
375 .WithEndpoint(endpoint)
376 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
377 .Execute();
378
379 string currentContent = "";
380 if (readResponseJson != null)
381 {
382 try
383 {
384 var valueRange = JsonSerializer.Deserialize<ValueRange>(readResponseJson);
385 currentContent = valueRange.values?.FirstOrDefault()?.FirstOrDefault()?.ToString() ?? "";
386 }
387 catch { }
388 }
389
390 string appendData = string.Join(InsertSeparator ?? "", ReadData?.SelectMany(row => row) ?? Enumerable.Empty<string>());
391 string newContent = currentContent + appendData;
392
393 var writeEndpoint = endpoint + googleSheetsConfig.ValueInputOption;
394 var writeResponse = SheetsRESTClient
395 .WithEndpoint(writeEndpoint)
396 .WithParameters(new { values = new List<List<string>> { new List<string> { newContent } } })
397 .WithHttpMethod("PUT")
398 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
399 .Execute();
400
401 if (writeResponse == null)
402 {
403 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to append to cell", this, GPALObjectType.GoogleSheets);
404 }
405 }
406
407 return this;
408 }
409
410 public IAllowDataOperations PrependToCell(string cell)
411 {
412 if (string.IsNullOrEmpty(cell))
413 {
414 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Cell reference cannot be empty", this, GPALObjectType.GoogleSheets);
415 return this;
416 }
417
418 if (string.IsNullOrEmpty(SpreadsheetId))
419 {
420 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Spreadsheet ID not set", this, GPALObjectType.GoogleSheets);
421 return this;
422 }
423
424 if (string.IsNullOrEmpty(SheetName))
425 {
426 SheetName = "Sheet1";
427 GPAL.PublishSimpleEvent(GPALEventType.INFO, "Using default sheet name 'Sheet1'", this, GPALObjectType.GoogleSheets);
428 }
429
430 if (ReadData == null)
431 {
432 if (!string.IsNullOrEmpty(ReadRange))
433 {
434 if (!ReadDataFromRange())
435 {
436 return this;
437 }
438 }
439 else
440 {
441 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No data to prepend; ReadData is null and no range specified", this, GPALObjectType.GoogleSheets);
442 return this;
443 }
444 }
445
446 if (googleSheetsConfig.UseAppsScript)
447 {
448 var response = CallWebApp("PrependToCell", new { spreadsheetId = SpreadsheetId, sheetName = SheetName, cell, data = ReadData, separator = InsertSeparator });
449 object successObj = null;
450 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
451 {
452 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to prepend to cell via Apps Script", this, GPALObjectType.GoogleSheets);
453 }
454 }
455 else
456 {
457 string endpoint = string.Format(googleSheetsConfig.ReadRangeEndpoint, SpreadsheetId, SheetName, cell);
458 var readResponseJson = SheetsRESTClient
459 .WithEndpoint(endpoint)
460 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
461 .Execute();
462
463 string currentContent = "";
464 if (readResponseJson != null)
465 {
466 try
467 {
468 var valueRange = JsonSerializer.Deserialize<ValueRange>(readResponseJson);
469 currentContent = valueRange.values?.FirstOrDefault()?.FirstOrDefault()?.ToString() ?? "";
470 }
471 catch { }
472 }
473
474 string prependData = string.Join(InsertSeparator ?? "", ReadData?.SelectMany(row => row) ?? Enumerable.Empty<string>());
475 string newContent = prependData + currentContent;
476
477 var writeEndpoint = endpoint + googleSheetsConfig.ValueInputOption;
478 var writeResponse = SheetsRESTClient
479 .WithEndpoint(writeEndpoint)
480 .WithParameters(new { values = new List<List<string>> { new List<string> { newContent } } })
481 .WithHttpMethod("PUT")
482 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
483 .Execute();
484
485 if (writeResponse == null)
486 {
487 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to prepend to cell", this, GPALObjectType.GoogleSheets);
488 }
489 }
490
491 return this;
492 }
493
494 public IAllowDataOperations InsertAtCell(string cell)
495 {
496 if (string.IsNullOrEmpty(cell))
497 {
498 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Cell reference cannot be empty", this, GPALObjectType.GoogleSheets);
499 return this;
500 }
501
502 if (string.IsNullOrEmpty(SpreadsheetId))
503 {
504 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Spreadsheet ID not set", this, GPALObjectType.GoogleSheets);
505 return this;
506 }
507
508 if (string.IsNullOrEmpty(SheetName))
509 {
510 SheetName = "Sheet1";
511 GPAL.PublishSimpleEvent(GPALEventType.INFO, "Using default sheet name 'Sheet1'", this, GPALObjectType.GoogleSheets);
512 }
513
514 if (ReadData == null)
515 {
516 if (!string.IsNullOrEmpty(ReadRange))
517 {
518 if (!ReadDataFromRange())
519 {
520 return this;
521 }
522 }
523 else
524 {
525 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No data to insert; ReadData is null and no range specified", this, GPALObjectType.GoogleSheets);
526 return this;
527 }
528 }
529
530 if (googleSheetsConfig.UseAppsScript)
531 {
532 var response = CallWebApp("InsertAtCell", new { spreadsheetId = SpreadsheetId, sheetName = SheetName, cell, data = ReadData, separator = InsertSeparator, position = InsertPosition });
533 object successObj = null;
534 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
535 {
536 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to insert at cell via Apps Script", this, GPALObjectType.GoogleSheets);
537 }
538 }
539 else
540 {
541 string endpoint = string.Format(googleSheetsConfig.ReadRangeEndpoint, SpreadsheetId, SheetName, cell);
542 var readResponseJson = SheetsRESTClient
543 .WithEndpoint(endpoint)
544 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
545 .Execute();
546
547 string currentContent = "";
548 if (readResponseJson != null)
549 {
550 try
551 {
552 var valueRange = JsonSerializer.Deserialize<ValueRange>(readResponseJson);
553 currentContent = valueRange.values?.FirstOrDefault()?.FirstOrDefault()?.ToString() ?? "";
554 }
555 catch { }
556 }
557
558 string insertData = string.Join(InsertSeparator ?? "", ReadData?.SelectMany(row => row) ?? Enumerable.Empty<string>());
559 int pos = Math.Min(InsertPosition ?? 0, currentContent.Length);
560 string newContent = currentContent.Substring(0, pos) + insertData + currentContent.Substring(pos);
561
562 var writeEndpoint = endpoint + googleSheetsConfig.ValueInputOption;
563 var writeResponse = SheetsRESTClient
564 .WithEndpoint(writeEndpoint)
565 .WithParameters(new { values = new List<List<string>> { new List<string> { newContent } } })
566 .WithHttpMethod("PUT")
567 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
568 .Execute();
569
570 if (writeResponse == null)
571 {
572 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to insert at cell", this, GPALObjectType.GoogleSheets);
573 }
574 }
575
576 return this;
577 }
578
579 public IAllowSheetOperations WithWriteRange(string writeRange)
580 {
581 if (string.IsNullOrEmpty(writeRange))
582 {
583 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Range cannot be empty", this, GPALObjectType.GoogleSheets);
584 return this;
585 }
586
587 WriteRange = writeRange;
588
589 return this;
590 }
591
592 public IAllowDataOperations WriteToSheet(object sheetNameOrId)
593 {
594 if (string.IsNullOrEmpty(SpreadsheetId))
595 {
596 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Spreadsheet ID not set", this, GPALObjectType.GoogleSheets);
597 return this;
598 }
599
600 string targetSheet = ResolveSheetName(sheetNameOrId);
601 if (string.IsNullOrEmpty(targetSheet))
602 {
603 return this;
604 }
605
606 if (ReadData == null)
607 {
608 if (!string.IsNullOrEmpty(ReadRange))
609 {
610 if (!ReadDataFromRange())
611 {
612 return this;
613 }
614 }
615 else
616 {
617 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No data to write; ReadData is null and no range specified", this, GPALObjectType.GoogleSheets);
618 return this;
619 }
620 }
621
622 if (googleSheetsConfig.UseAppsScript)
623 {
624 var response = CallWebApp("WriteRange", new { spreadsheetId = SpreadsheetId, sheetName = targetSheet, range = WriteRange, data = ReadData });
625 object successObj = null;
626 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
627 {
628 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to write range via Apps Script", this, GPALObjectType.GoogleSheets);
629 }
630 }
631 else
632 {
633 string endpoint = string.Format(googleSheetsConfig.WriteRangeEndpoint, SpreadsheetId, targetSheet, WriteRange) + googleSheetsConfig.ValueInputOption;
634 var response = SheetsRESTClient
635 .WithEndpoint(endpoint)
636 .WithParameters(new { values = ReadData })
637 .WithHttpMethod("PUT")
638 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
639 .Execute();
640
641 if (response == null)
642 {
643 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to write range", this, GPALObjectType.GoogleSheets);
644 }
645 }
646
647 return this;
648 }
649
650 public IAllowDataOperations AppendToSheet(object sheetNameOrId)
651 {
652 object targetSheetOrId = ResolveSheetIdOrName(sheetNameOrId);
653 if (targetSheetOrId == null)
654 {
655 return this;
656 }
657
658 if (string.IsNullOrEmpty(SpreadsheetId))
659 {
660 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Spreadsheet ID not set", this, GPALObjectType.GoogleSheets);
661 return this;
662 }
663
664 if (ReadData == null)
665 {
666 if (!string.IsNullOrEmpty(ReadRange))
667 {
668 if (!ReadDataFromRange())
669 {
670 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No data to append.", this, GPALObjectType.GoogleSheets);
671 return this;
672 }
673 }
674 else
675 {
676 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No data to append.", this, GPALObjectType.GoogleSheets);
677 return this;
678 }
679 }
680
681 if (googleSheetsConfig.UseAppsScript)
682 {
683 var response = CallWebApp("AppendToSheet", new { spreadsheetId = SpreadsheetId, sheetNameOrId = targetSheetOrId, data = ReadData });
684 object successObj = null;
685 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
686 {
687 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to append to sheet via Apps Script", this, GPALObjectType.GoogleSheets);
688 }
689 }
690 else
691 {
692 string targetSheetName = ResolveSheetName(sheetNameOrId);
693 if (string.IsNullOrEmpty(targetSheetName))
694 {
695 return this;
696 }
697 string endpoint = string.Format(googleSheetsConfig.AppendRangeEndpoint, SpreadsheetId, targetSheetName, "A1") + "?valueInputOption=RAW&insertDataOption=INSERT_ROWS";
698 var response = SheetsRESTClient
699 .WithEndpoint(endpoint)
700 .WithParameters(new { values = ReadData })
701 .WithHttpMethod("POST")
702 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
703 .Execute();
704
705 if (response == null)
706 {
707 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to append to sheet", this, GPALObjectType.GoogleSheets);
708 }
709 }
710
711 return this;
712 }
713 #endregion NEW
714
724 private const string GatewayScriptResource = "GenerallyPositive.Browser.gcode.js";
725
726 private string ReadGatewayScript()
727 {
728 string retVal = null;
729
730 using (Stream stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(GatewayScriptResource))
731 {
732 if (null == stream)
733 {
734 string msg = $"[{GatewayScriptResource}] is not in this assembly, so there is no gateway script to deploy";
735
736 GPAL.PublishSimpleEvent(GPALEventType.ERROR, msg, this, GPALObjectType.GoogleSheets);
737
738 throw new GPALException($"{GPAL.MyMethodName()}: {msg}");
739 }
740
741 using (StreamReader reader = new StreamReader(stream))
742 retVal = reader.ReadToEnd();
743 }
744
745 return retVal;
746 }
747
748 private void SetupAndDeployScript(string spreadsheetIdOrTitle)
749 {
750 string cloudProjectId = SetupCloudProject($"GPAL_Sheets_{Guid.NewGuid().ToString().Substring(0, 8)}");
751 if (string.IsNullOrEmpty(cloudProjectId))
752 {
753 return;
754 }
755
756 string scriptTitle = $"GPAL_Script_{spreadsheetIdOrTitle}";
757 string scriptId = GetOrCreateScriptProject(cloudProjectId, scriptTitle);
758 if (string.IsNullOrEmpty(scriptId))
759 {
760 return;
761 }
762
763 // Check for existing deployment
764 var deploymentResponse = ScriptsRESTClient
765 .WithEndpoint($"/v1/projects/{scriptId}/deployments")
766 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
767 .Execute();
768
769 if (deploymentResponse != null)
770 {
771 try
772 {
773 var deploymentData = JsonSerializer.Deserialize<Dictionary<string, object>>(deploymentResponse);
774 object deploymentsObj = null;
775 if (deploymentData.TryGetValue("deployments", out deploymentsObj))
776 {
777 var deployments = JsonSerializer.Deserialize<JsonElement>(deploymentsObj.ToString());
778 foreach (var deployment in deployments.EnumerateArray())
779 {
780 if (deployment.TryGetProperty("entryPoints", out var entryPoints))
781 {
782 foreach (var entry in entryPoints.EnumerateArray())
783 {
784 if (entry.TryGetProperty("webApp", out var webApp) && webApp.TryGetProperty("url", out var url))
785 {
786 googleSheetsConfig.WebAppUrl = url.GetString();
787 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Found existing web app at: [{googleSheetsConfig.WebAppUrl}]", this, GPALObjectType.GoogleSheets);
788 AppsScriptProjectId = scriptId;
789 return;
790 }
791 }
792 }
793 }
794 }
795 }
796 catch { }
797 }
798
799 // Hardcode the Apps Script content (or load from embedded resource)
800 string scriptContent = ReadGatewayScript();
801
802 this.WithProjectId(scriptId)
803 .WithScriptName("main")
804 .WithScriptAccess(ScriptAccessType.ME)
805 .WithExecuteAs(ExecuteAsType.USER_DEPLOYING)
806 .UploadScript(scriptContent)
807 .DeployScriptAsWebApp(out string webAppUrl);
808
809 if (!string.IsNullOrEmpty(webAppUrl))
810 {
811 googleSheetsConfig.WebAppUrl = webAppUrl;
812 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Apps Script deployed at: [{webAppUrl}]", this, GPALObjectType.GoogleSheets);
813 }
814 }
815
826 public IAllowSpreadsheetSelection WithWebAppUrl(string webAppUrl)
827 {
828 if (true == string.IsNullOrWhiteSpace(webAppUrl))
829 {
830 string msg = "No web app url was supplied, so there is no gateway to drive sheets through";
831
832 GPAL.PublishSimpleEvent(GPALEventType.ERROR, msg, this, GPALObjectType.GoogleSheets);
833
834 throw new GPALException($"{GPAL.MyMethodName()}: {msg}");
835 }
836
837 googleSheetsConfig.WebAppUrl = webAppUrl;
838
839 // named outright rather than worked out. InitializeMode only reaches this mode by failing to
840 // validate a token, and there is no token on this route to fail
841 googleSheetsConfig.UseAppsScript = true;
842
843 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Sheets run through the web app at [{webAppUrl}]", this, GPALObjectType.GoogleSheets);
844
845 return this;
846 }
847
848 public IAllowSpreadsheetSelection WithCredentials(ICredentials credentials)
849 {
850 Credentials = credentials;
851 Credentials.FetchAccessToken(out string accessToken);
852 InitializeMode();
853 return this;
854 }
855
856 public IAllowSheetSelection WithSpreadsheet(string spreadsheetIdOrTitle)
857 {
858 if (string.IsNullOrEmpty(spreadsheetIdOrTitle))
859 {
860 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Spreadsheet ID or title cannot be empty", this, GPALObjectType.GoogleSheets);
861 return this;
862 }
863
864 InitializeMode();
865
866 if (googleSheetsConfig.UseAppsScript)
867 {
868 if (string.IsNullOrEmpty(googleSheetsConfig.WebAppUrl))
869 {
870 SetupAndDeployScript(spreadsheetIdOrTitle);
871 }
872
873 var response = CallWebApp("WithSpreadsheet", new { spreadsheetIdOrTitle });
874 object successObj = null;
875 object idObj = null;
876 if (response != null && response.TryGetValue("success", out successObj) && successObj is bool && (bool)successObj && response.TryGetValue("spreadsheetId", out idObj))
877 {
878 SpreadsheetId = idObj.ToString();
879 }
880 else
881 {
882 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to open or create spreadsheet [{spreadsheetIdOrTitle}] via Apps Script", this, GPALObjectType.GoogleSheets);
883 }
884 }
885 else
886 {
887 var responseJson = SheetsRESTClient
888 .WithEndpoint($"{googleSheetsConfig.SpreadsheetEndPoint}{spreadsheetIdOrTitle}")
889 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
890 .Execute();
891
892 if (!string.IsNullOrEmpty(responseJson))
893 {
894 SpreadsheetId = spreadsheetIdOrTitle;
895 }
896 else
897 {
898 var createRequest = new { properties = new { title = spreadsheetIdOrTitle } };
899 var createResponseJson = SheetsRESTClient
900 .WithEndpoint("/v4/spreadsheets")
901 .WithParameters(createRequest)
902 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
903 .WithHttpMethod("POST")
904 .Execute();
905
906 if (createResponseJson != null)
907 {
908 var createResponse = JsonSerializer.Deserialize<SpreadsheetResponse>(createResponseJson);
909 if (!string.IsNullOrEmpty(createResponse?.SpreadsheetId))
910 {
911 SpreadsheetId = createResponse.SpreadsheetId;
912 }
913 else
914 {
915 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to create spreadsheet [{spreadsheetIdOrTitle}]", this, GPALObjectType.GoogleSheets);
916 }
917 }
918 else
919 {
920 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to create spreadsheet [{spreadsheetIdOrTitle}]", this, GPALObjectType.GoogleSheets);
921 }
922 }
923 }
924
925 return this;
926 }
927
928 public IAllowSheetOperations CreateSheet(string sheetName)
929 {
930 if (string.IsNullOrEmpty(sheetName))
931 {
932 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Sheet name cannot be empty", this, GPALObjectType.GoogleSheets);
933 return this;
934 }
935
936 if (googleSheetsConfig.UseAppsScript)
937 {
938 var response = CallWebApp("CreateSheet", new { spreadsheetId = SpreadsheetId, sheetName });
939 object successObj = null;
940 if (response != null && response.TryGetValue("success", out successObj) && successObj is bool && (bool)successObj)
941 {
942 SheetName = sheetName;
943 }
944 else
945 {
946 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to create sheet via Apps Script", this, GPALObjectType.GoogleSheets);
947 }
948 }
949 else
950 {
951 var request = new
952 {
953 requests = new[] { new { addSheet = new { properties = new { title = sheetName } } } }
954 };
955 var response = SheetsRESTClient
956 .WithEndpoint(string.Format(googleSheetsConfig.FormatRangeEndpoint, SpreadsheetId))
957 .WithParameters(request)
958 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
959 .Execute();
960
961 if (response == null)
962 {
963 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to create sheet", this, GPALObjectType.GoogleSheets);
964 }
965
966 SheetName = sheetName;
967 }
968
969 return this;
970 }
971
972 public IAllowSheetSelection SetSheetName(string newSheetName)
973 {
974 if (string.IsNullOrEmpty(newSheetName))
975 {
976 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Title cannot be empty", this, GPALObjectType.GoogleSheets);
977 return this;
978 }
979
980 if (googleSheetsConfig.UseAppsScript)
981 {
982 var response = CallWebApp("WithTitle", new { spreadsheetId = SpreadsheetId, sheetName = SheetName, title = newSheetName });
983 object successObj = null;
984 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
985 {
986 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to set sheet title via Apps Script", this, GPALObjectType.GoogleSheets);
987 }
988 }
989 else
990 {
991 var request = new
992 {
993 requests = new[] { new { updateSheetProperties = new { properties = new { title = newSheetName }, fields = googleSheetsConfig.TitleField } } }
994 };
995 var response = SheetsRESTClient
996 .WithEndpoint(string.Format(googleSheetsConfig.FormatRangeEndpoint, SpreadsheetId))
997 .WithParameters(request)
998 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
999 .Execute();
1000
1001 if (response == null)
1002 {
1003 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to set sheet title", this, GPALObjectType.GoogleSheets);
1004 }
1005 }
1006
1007 return this;
1008 }
1009 public IAllowSheetSelection SetSpreadsheetTitle(string title)
1010 {
1011 if (string.IsNullOrEmpty(title))
1012 {
1013 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Title cannot be empty", this, GPALObjectType.GoogleSheets);
1014 return this;
1015 }
1016
1017 if (googleSheetsConfig.UseAppsScript)
1018 {
1019 var response = CallWebApp("WithTitle", new { spreadsheetId = SpreadsheetId, sheetName = SheetName, title });
1020 object successObj = null;
1021 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
1022 {
1023 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to set sheet title via Apps Script", this, GPALObjectType.GoogleSheets);
1024 }
1025 }
1026 else
1027 {
1028 var request = new
1029 {
1030 requests = new[] { new { updateSpreadsheetProperties = new { properties = new { title }, fields = googleSheetsConfig.TitleField } } }
1031 };
1032 var response = SheetsRESTClient
1033 .WithEndpoint(string.Format(googleSheetsConfig.FormatRangeEndpoint, SpreadsheetId))
1034 .WithParameters(request)
1035 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1036 .Execute();
1037
1038 if (response == null)
1039 {
1040 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to set sheet title", this, GPALObjectType.GoogleSheets);
1041 }
1042 }
1043
1044 return this;
1045 }
1046
1047 public IAllowInDataOperations WithSheet(object sheetNameOrId)
1048 {
1049 if (sheetNameOrId == null)
1050 {
1051 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Sheet name or ID cannot be null", this, GPALObjectType.GoogleSheets);
1052 return this;
1053 }
1054
1055 string sheetName = null;
1056 int? sheetId = null;
1057
1058 if (sheetNameOrId is string name)
1059 {
1060 if (string.IsNullOrEmpty(name))
1061 {
1062 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Sheet name cannot be empty", this, GPALObjectType.GoogleSheets);
1063 return this;
1064 }
1065 sheetName = name;
1066 }
1067 else if (sheetNameOrId is int id)
1068 {
1069 sheetId = id;
1070 }
1071 else
1072 {
1073 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Invalid sheet name or ID type", this, GPALObjectType.GoogleSheets);
1074 return this;
1075 }
1076
1077 if (googleSheetsConfig.UseAppsScript)
1078 {
1079 var action = sheetId.HasValue ? "WithSheetById" : "WithSheet";
1080 var response = CallWebApp(action, new { spreadsheetId = SpreadsheetId, sheetName, sheetId });
1081 object successObj = null;
1082 object nameObj = null;
1083 if (response != null && response.TryGetValue("success", out successObj) && successObj is bool && (bool)successObj)
1084 {
1085 if (response.TryGetValue("sheetName", out nameObj))
1086 {
1087 SheetName = nameObj.ToString();
1088 }
1089 else
1090 {
1091 SheetName = sheetName;
1092 }
1093 }
1094 else
1095 {
1096 GPAL.PublishSimpleEvent(GPALEventType.ERROR,
1097 $"Failed to activate sheet {(sheetId.HasValue ? $"with ID {sheetId}" : $"named {sheetName}")} via Apps Script",
1098 this, GPALObjectType.GoogleSheets);
1099 }
1100 }
1101 else
1102 {
1103 var spreadsheetResponseJson = SheetsRESTClient
1104 .WithEndpoint($"{googleSheetsConfig.SpreadsheetEndPoint}{SpreadsheetId}")
1105 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1106 .Execute();
1107
1108 SpreadsheetResponse spreadsheetResponse = null;
1109 if (spreadsheetResponseJson != null)
1110 {
1111 try
1112 {
1113 spreadsheetResponse = JsonSerializer.Deserialize<SpreadsheetResponse>(spreadsheetResponseJson);
1114 }
1115 catch { }
1116 }
1117
1118 if (spreadsheetResponse == null || spreadsheetResponse.Sheets == null)
1119 {
1120 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to retrieve spreadsheet info", this, GPALObjectType.GoogleSheets);
1121 return this;
1122 }
1123
1124 var targetSheet = sheetId.HasValue
1125 ? spreadsheetResponse.Sheets.FirstOrDefault(s => s.Properties.SheetId == sheetId.Value)
1126 : spreadsheetResponse.Sheets.FirstOrDefault(s => s.Properties.Title.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
1127
1128 if (targetSheet == null)
1129 {
1130 if (sheetId.HasValue)
1131 {
1132 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Sheet with ID [{sheetId}] not found", this, GPALObjectType.GoogleSheets);
1133 return this;
1134 }
1135 else
1136 {
1137 CreateSheet(sheetName);
1138 targetSheet = spreadsheetResponse.Sheets.FirstOrDefault(s => s.Properties.Title.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
1139 if (targetSheet == null)
1140 {
1141 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to create sheet [{sheetName}]", this, GPALObjectType.GoogleSheets);
1142 return this;
1143 }
1144 }
1145 }
1146
1147 int targetSheetId = targetSheet.Properties.SheetId;
1148 SheetName = targetSheet.Properties.Title;
1149
1150 var activateRequest = new
1151 {
1152 requests = new[]
1153 {
1154 new
1155 {
1156 updateSheetProperties = new
1157 {
1158 properties = new { sheetId = targetSheetId, hidden = false },
1159 fields = googleSheetsConfig.HiddenField
1160 }
1161 }
1162 }
1163 };
1164
1165 var activateResponse = SheetsRESTClient
1166 .WithEndpoint(string.Format(googleSheetsConfig.FormatRangeEndpoint, SpreadsheetId))
1167 .WithParameters(activateRequest)
1168 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1169 .Execute();
1170
1171 if (activateResponse == null)
1172 {
1173 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to activate sheet", this, GPALObjectType.GoogleSheets);
1174 }
1175 }
1176
1177 return this;
1178 }
1179
1180 public IAllowDataOperations WithReadRange(string readRange)
1181 {
1182 if (string.IsNullOrEmpty(readRange))
1183 {
1184 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Range cannot be empty", this, GPALObjectType.GoogleSheets);
1185 return this;
1186 }
1187
1188 ReadRange = readRange;
1189 return this;
1190 }
1191
1192 public IAllowDataOperations WithData(IGPALGrid<string> data)
1193 {
1194 ReadData = data;
1195 return this;
1196 }
1197
1198 public IAllowSheetSelection DeleteSheet(object sheetNameOrId)
1199 {
1200 if (sheetNameOrId == null)
1201 {
1202 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Sheet name or ID cannot be null", this, GPALObjectType.GoogleSheets);
1203 return this;
1204 }
1205
1206 string sheetName = null;
1207 int? sheetId = null;
1208
1209 if (sheetNameOrId is string name)
1210 {
1211 if (string.IsNullOrEmpty(name))
1212 {
1213 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Sheet name cannot be empty", this, GPALObjectType.GoogleSheets);
1214 return this;
1215 }
1216 sheetName = name;
1217 }
1218 else if (sheetNameOrId is int id)
1219 {
1220 sheetId = id;
1221 }
1222 else
1223 {
1224 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Invalid sheet name or ID type", this, GPALObjectType.GoogleSheets);
1225 return this;
1226 }
1227
1228 if (googleSheetsConfig.UseAppsScript)
1229 {
1230 var action = sheetId.HasValue ? "DeleteSheetById" : "DeleteSheet";
1231 var response = CallWebApp(action, new { spreadsheetId = SpreadsheetId, sheetName, sheetId });
1232 object successObj = null;
1233 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
1234 {
1235 GPAL.PublishSimpleEvent(GPALEventType.ERROR,
1236 $"Failed to delete sheet {(sheetId.HasValue ? $"with ID {sheetId}" : $"named {sheetName}")} via Apps Script",
1237 this, GPALObjectType.GoogleSheets);
1238 }
1239 }
1240 else
1241 {
1242 var spreadsheetResponseJson = SheetsRESTClient
1243 .WithEndpoint($"{googleSheetsConfig.SpreadsheetEndPoint}{SpreadsheetId}")
1244 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1245 .Execute();
1246
1247 SpreadsheetResponse spreadsheetResponse = null;
1248 if (spreadsheetResponseJson != null)
1249 {
1250 try
1251 {
1252 spreadsheetResponse = JsonSerializer.Deserialize<SpreadsheetResponse>(spreadsheetResponseJson);
1253 }
1254 catch { }
1255 }
1256
1257 if (spreadsheetResponse == null || spreadsheetResponse.Sheets == null)
1258 {
1259 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to retrieve spreadsheet info", this, GPALObjectType.GoogleSheets);
1260 return this;
1261 }
1262
1263 var targetSheet = sheetId.HasValue
1264 ? spreadsheetResponse.Sheets.FirstOrDefault(s => s.Properties.SheetId == sheetId.Value)
1265 : spreadsheetResponse.Sheets.FirstOrDefault(s => s.Properties.Title.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
1266
1267 if (targetSheet == null)
1268 {
1269 GPAL.PublishSimpleEvent(GPALEventType.ERROR,
1270 $"{(sheetId.HasValue ? $"Sheet with ID {sheetId}" : $"Sheet '{sheetName}'")} not found",
1271 this, GPALObjectType.GoogleSheets);
1272 return this;
1273 }
1274
1275 var request = new
1276 {
1277 requests = new[] { new { deleteSheet = new { sheetId = targetSheet.Properties.SheetId } } }
1278 };
1279 var response = SheetsRESTClient
1280 .WithEndpoint(string.Format(googleSheetsConfig.FormatRangeEndpoint, SpreadsheetId))
1281 .WithParameters(request)
1282 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1283 .Execute();
1284
1285 if (response == null)
1286 {
1287 GPAL.PublishSimpleEvent(GPALEventType.ERROR,
1288 $"Failed to delete sheet {(sheetId.HasValue ? $"with ID {sheetId}" : $"named {sheetName}")}",
1289 this, GPALObjectType.GoogleSheets);
1290 }
1291 }
1292
1293 return this;
1294 }
1295
1296 public IAllowSheetSelection ListSheets(out IEnumerable<(string name, int sheetId)> sheets)
1297 {
1298 if (googleSheetsConfig.UseAppsScript)
1299 {
1300 var response = CallWebApp("ListSheets", new { spreadsheetId = SpreadsheetId });
1301 object successObj = null;
1302 object sheetsObj = null;
1303 if (response != null && response.TryGetValue("success", out successObj) && successObj is bool && (bool)successObj && response.TryGetValue("sheets", out sheetsObj))
1304 {
1305 try
1306 {
1307 var sheetsElement = JsonSerializer.Deserialize<JsonElement>(sheetsObj.ToString());
1308 sheets = sheetsElement.EnumerateArray().Select(s => (s.GetProperty("name").GetString(), s.GetProperty("sheetId").GetInt32()));
1309 return this;
1310 }
1311 catch
1312 {
1313 sheets = Enumerable.Empty<(string, int)>();
1314 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to parse sheets list via Apps Script", this, GPALObjectType.GoogleSheets);
1315 return this;
1316 }
1317 }
1318 else
1319 {
1320 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to list sheets via Apps Script", this, GPALObjectType.GoogleSheets);
1321 sheets = Enumerable.Empty<(string, int)>();
1322 return this;
1323 }
1324 }
1325 else
1326 {
1327 var responseJson = SheetsRESTClient
1328 .WithEndpoint($"{googleSheetsConfig.SpreadsheetEndPoint}{SpreadsheetId}")
1329 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1330 .Execute();
1331
1332 if (responseJson == null)
1333 {
1334 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to list sheets", this, GPALObjectType.GoogleSheets);
1335 sheets = Enumerable.Empty<(string, int)>();
1336 return this;
1337 }
1338
1339 try
1340 {
1341 var response = JsonSerializer.Deserialize<SpreadsheetResponse>(responseJson);
1342 if (response?.Sheets != null)
1343 {
1344 sheets = response.Sheets.Select(s => (s.Properties.Title, s.Properties.SheetId));
1345 }
1346 else
1347 {
1348 sheets = Enumerable.Empty<(string, int)>();
1349 }
1350 }
1351 catch
1352 {
1353 sheets = Enumerable.Empty<(string, int)>();
1354 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to parse sheets list", this, GPALObjectType.GoogleSheets);
1355 }
1356
1357 return this;
1358 }
1359 }
1360
1361 public IAllowInsertSettings WithInsertPosition(int position)
1362 {
1363 InsertPosition = position;
1364 return this;
1365 }
1366
1367 public IAllowInsertSettings WithInsertSeparator(string separator)
1368 {
1369 InsertSeparator = separator;
1370 return this;
1371 }
1372
1373 public IAllowDataOperations CalculateSum(out string result)
1374 {
1375 if (ReadData == null && !string.IsNullOrEmpty(ReadRange))
1376 {
1377 ReadDataFromRange();
1378 }
1379
1380 if (googleSheetsConfig.UseAppsScript)
1381 {
1382 var response = CallWebApp("CalculateSum", new { data = ReadData });
1383 object successObj = null;
1384 object resultObj = null;
1385 if (response != null && response.TryGetValue("success", out successObj) && successObj is bool && (bool)successObj && response.TryGetValue("result", out resultObj))
1386 {
1387 result = resultObj.ToString();
1388 }
1389 else
1390 {
1391 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to calculate sum via Apps Script", this, GPALObjectType.GoogleSheets);
1392 result = "0";
1393 }
1394 }
1395 else
1396 {
1397 result = ReadData?.SelectMany(row => row)
1398 .Where(v => double.TryParse(v, out _))
1399 .Sum(v => double.Parse(v))
1400 .ToString() ?? "0";
1401 }
1402
1403 return this;
1404 }
1405
1406 public IAllowDataOperations CalculateCount(out string result)
1407 {
1408 if (ReadData == null && !string.IsNullOrEmpty(ReadRange))
1409 {
1410 ReadDataFromRange();
1411 }
1412
1413 if (googleSheetsConfig.UseAppsScript)
1414 {
1415 var response = CallWebApp("CalculateCount", new { data = ReadData });
1416 object successObj = null;
1417 object resultObj = null;
1418 if (response != null && response.TryGetValue("success", out successObj) && successObj is bool && (bool)successObj && response.TryGetValue("result", out resultObj))
1419 {
1420 result = resultObj.ToString();
1421 }
1422 else
1423 {
1424 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to calculate count via Apps Script", this, GPALObjectType.GoogleSheets);
1425 result = "0";
1426 }
1427 }
1428 else
1429 {
1430 result = ReadData?.SelectMany(row => row).Count().ToString() ?? "0";
1431 }
1432
1433 return this;
1434 }
1435
1436 public IAllowFormatSettings WithFormatType(FormatType formatType)
1437 {
1438 OurFormatType = formatType;
1439 return this;
1440 }
1441
1442 public IAllowFormatRange WithFormatValue(object formatValue)
1443 {
1444 FormatValue = formatValue;
1445 return this;
1446 }
1447
1448 public IAllowFormatRange WithAlignmentFormatValue(HorizontalAlignmentType alignment)
1449 {
1450 FormatValue = alignment;
1451 return this;
1452 }
1453
1454 public IAllowFormatRange WithAlignmentFormatValue(VerticalAlignmentType alignment)
1455 {
1456 FormatValue = alignment;
1457 return this;
1458 }
1459
1460 public IAllowFormatRange WithNumberFormatValue(NumberFormatType numberFormat)
1461 {
1462 NumberFormatType = numberFormat;
1463 return this;
1464 }
1465
1466 public IAllowFormatRange WithTextRotationFormatValue(TextRotationType rotation)
1467 {
1468 FormatValue = rotation;
1469 return this;
1470 }
1471
1472 public IAllowFormatRange WithColorFormatValue(System.Drawing.Color color)
1473 {
1474 FormatValue = color;
1475 return this;
1476 }
1477
1478 public IAllowInDataOperations FormatRange(string range)
1479 {
1480 if (string.IsNullOrEmpty(range))
1481 {
1482 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Range cannot be empty", this, GPALObjectType.GoogleSheets);
1483 return this;
1484 }
1485
1486 if (OurFormatType == null)
1487 {
1488 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Format type not set", this, GPALObjectType.GoogleSheets);
1489 return this;
1490 }
1491
1492 if (googleSheetsConfig.UseAppsScript)
1493 {
1494 var response = CallWebApp("FormatRange", new
1495 {
1496 spreadsheetId = SpreadsheetId,
1497 sheetName = SheetName,
1498 range,
1499 formatType = OurFormatType.ToString(),
1500 formatValue = FormatValue?.ToString(),
1501 numberFormatType = NumberFormatType?.ToString(),
1502 numberFormatPattern = NumberFormatPattern
1503 });
1504 object successObj = null;
1505 if (response == null || !response.TryGetValue("success", out successObj) || !(successObj is bool) || !(bool)successObj)
1506 {
1507 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to format range via Apps Script", this, GPALObjectType.GoogleSheets);
1508 }
1509 }
1510 else
1511 {
1512 var (targetSheetId, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex) = ParseRange(range);
1513 var formatRequest = new
1514 {
1515 requests = new[]
1516 {
1517 new
1518 {
1519 repeatCell = new
1520 {
1521 range = new { sheetId = targetSheetId, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex },
1522 cell = GetCellFormat(),
1523 fields = GetFieldsForFormatType(OurFormatType.Value)
1524 }
1525 }
1526 }
1527 };
1528 var response = SheetsRESTClient
1529 .WithEndpoint(string.Format(googleSheetsConfig.FormatRangeEndpoint, SpreadsheetId))
1530 .WithParameters(formatRequest)
1531 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1532 .Execute();
1533
1534 if (response == null)
1535 {
1536 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to format range", this, GPALObjectType.GoogleSheets);
1537 }
1538 }
1539
1540 return this;
1541 }
1542
1543 private object GetCellFormat()
1544 {
1545 if (FormatValue == null)
1546 {
1547 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Format value not set for [{OurFormatType}]", this, GPALObjectType.GoogleSheets);
1548 return new { userEnteredFormat = new { } };
1549 }
1550
1551 switch (OurFormatType)
1552 {
1553 case FormatType.Bold:
1554 if (FormatValue is bool bold)
1555 return new { userEnteredFormat = new { textFormat = new { bold } } };
1556 break;
1557 case FormatType.Italic:
1558 if (FormatValue is bool italic)
1559 return new { userEnteredFormat = new { textFormat = new { italic } } };
1560 break;
1561 case FormatType.Underline:
1562 if (FormatValue is bool underline)
1563 return new { userEnteredFormat = new { textFormat = new { underline } } };
1564 break;
1565 case FormatType.Strikethrough:
1566 if (FormatValue is bool strikethrough)
1567 return new { userEnteredFormat = new { textFormat = new { strikethrough } } };
1568 break;
1569 case FormatType.FontSize:
1570 if (FormatValue is int fontSize)
1571 return new { userEnteredFormat = new { textFormat = new { fontSize } } };
1572 break;
1573 case FormatType.FontFamily:
1574 if (FormatValue is string fontFamily)
1575 return new { userEnteredFormat = new { textFormat = new { fontFamily } } };
1576 break;
1577 case FormatType.ForegroundColor:
1578 if (FormatValue is System.Drawing.Color color)
1579 return new { userEnteredFormat = new { textFormat = new { foregroundColor = new { red = color.R / 255.0, green = color.G / 255.0, blue = color.B / 255.0 } } } };
1580 break;
1581 case FormatType.CellBackgroundColor:
1582 if (FormatValue is System.Drawing.Color bgColor)
1583 return new { userEnteredFormat = new { backgroundColor = new { red = bgColor.R / 255.0, green = bgColor.G / 255.0, blue = bgColor.B / 255.0 } } };
1584 break;
1585 case FormatType.NumberFormat:
1586 if (NumberFormatType != null)
1587 return new
1588 {
1589 userEnteredFormat = new
1590 {
1591 numberFormat = new
1592 {
1593 type = NumberFormatType.Value.ToString().ToUpper(),
1594 pattern = string.IsNullOrEmpty(NumberFormatPattern) ? null : NumberFormatPattern
1595 }
1596 }
1597 };
1598 break;
1599 case FormatType.HorizontalAlignment:
1600 if (FormatValue is HorizontalAlignmentType hAlign)
1601 return new { userEnteredFormat = new { horizontalAlignment = hAlign.ToString().ToUpper() } };
1602 break;
1603 case FormatType.VerticalAlignment:
1604 if (FormatValue is VerticalAlignmentType vAlign)
1605 return new { userEnteredFormat = new { verticalAlignment = vAlign.ToString().ToUpper() } };
1606 break;
1607 case FormatType.TextRotation:
1608 if (FormatValue is TextRotationType rotation)
1609 {
1610 object textRotation = rotation switch
1611 {
1612 TextRotationType.TiltUp30 => new { degrees = 30 },
1613 TextRotationType.TiltUp45 => new { degrees = 45 },
1614 TextRotationType.TiltDown30 => new { degrees = -30 },
1615 TextRotationType.TiltDown45 => new { degrees = -45 },
1616 TextRotationType.UpsideDown => new { degrees = -180 },
1617 TextRotationType.Vertical => new { vertical = true },
1618 _ => new { degrees = 0 }
1619 };
1620 return new { userEnteredFormat = new { textFormat = new { textRotation } } };
1621 }
1622 break;
1623 }
1624
1625 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Invalid format value for [{OurFormatType}]", this, GPALObjectType.GoogleSheets);
1626 return new { userEnteredFormat = new { } };
1627 }
1628
1629 private string GetFieldsForFormatType(FormatType formatType)
1630 {
1631 string subfield;
1632 switch (formatType)
1633 {
1634 case FormatType.Bold:
1635 case FormatType.Italic:
1636 case FormatType.Underline:
1637 case FormatType.Strikethrough:
1638 case FormatType.FontSize:
1639 case FormatType.FontFamily:
1640 case FormatType.ForegroundColor:
1641 subfield = "textFormat";
1642 break;
1643 case FormatType.CellBackgroundColor:
1644 subfield = "backgroundColor";
1645 break;
1646 case FormatType.NumberFormat:
1647 subfield = "numberFormat";
1648 break;
1649 case FormatType.HorizontalAlignment:
1650 subfield = "horizontalAlignment";
1651 break;
1652 case FormatType.VerticalAlignment:
1653 subfield = "verticalAlignment";
1654 break;
1655 case FormatType.TextRotation:
1656 subfield = "textFormat.textRotation";
1657 break;
1658 default:
1659 subfield = "";
1660 break;
1661 }
1662 ;
1663 return string.IsNullOrEmpty(subfield) ? googleSheetsConfig.UserEnteredFormatField : $"{googleSheetsConfig.UserEnteredFormatField}.{subfield}";
1664 }
1665
1666 private (int sheetId, int? startRowIndex, int? endRowIndex, int? startColumnIndex, int? endColumnIndex) ParseRange(string range)
1667 {
1668 int sheetId = 0; // Default to first sheet if no sheet specified
1669 string sheetNameFromRange = null;
1670 string rangePart = range;
1671
1672 if (range.Contains("!"))
1673 {
1674 var parts = range.Split('!');
1675 sheetNameFromRange = parts[0].Trim('\'');
1676 rangePart = parts[1];
1677 }
1678
1679 if (!string.IsNullOrEmpty(sheetNameFromRange))
1680 {
1681 var spreadsheetResponseJson = SheetsRESTClient
1682 .WithEndpoint($"{googleSheetsConfig.SpreadsheetEndPoint}{SpreadsheetId}")
1683 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1684 .Execute();
1685
1686 if (spreadsheetResponseJson != null)
1687 {
1688 try
1689 {
1690 var spreadsheetResponse = JsonSerializer.Deserialize<SpreadsheetResponse>(spreadsheetResponseJson);
1691 var targetSheet = spreadsheetResponse?.Sheets?.FirstOrDefault(s => s.Properties.Title.Equals(sheetNameFromRange, StringComparison.OrdinalIgnoreCase));
1692 if (targetSheet != null)
1693 {
1694 sheetId = targetSheet.Properties.SheetId;
1695 }
1696 }
1697 catch { }
1698 }
1699 }
1700 else if (!string.IsNullOrEmpty(SheetName))
1701 {
1702 // Use current SheetName to get sheetId
1703 var spreadsheetResponseJson = SheetsRESTClient
1704 .WithEndpoint($"{googleSheetsConfig.SpreadsheetEndPoint}{SpreadsheetId}")
1705 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1706 .Execute();
1707
1708 if (spreadsheetResponseJson != null)
1709 {
1710 try
1711 {
1712 var spreadsheetResponse = JsonSerializer.Deserialize<SpreadsheetResponse>(spreadsheetResponseJson);
1713 var targetSheet = spreadsheetResponse?.Sheets?.FirstOrDefault(s => s.Properties.Title.Equals(SheetName, StringComparison.OrdinalIgnoreCase));
1714 if (targetSheet != null)
1715 {
1716 sheetId = targetSheet.Properties.SheetId;
1717 }
1718 }
1719 catch { }
1720 }
1721 }
1722
1723 // Improved regex to handle more A1 notation cases: A1, A1:B2, A:A, 1:1, A1:A, etc.
1724 var match = System.Text.RegularExpressions.Regex.Match(rangePart, @"^([A-Z]+)?(\d+)?(?::([A-Z]+)?(\d+)?)?$");
1725 if (!match.Success)
1726 {
1727 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Invalid range format: [{range}]", this, GPALObjectType.GoogleSheets);
1728 return (sheetId, null, null, null, null);
1729 }
1730
1731 string startCol = match.Groups[1].Value;
1732 string startRowStr = match.Groups[2].Value;
1733 string endCol = match.Groups[3].Value;
1734 string endRowStr = match.Groups[4].Value;
1735 int? startRowIndex;
1736 if (string.IsNullOrEmpty(startRowStr))
1737 {
1738 startRowIndex = null;
1739 }
1740 else
1741 {
1742 startRowIndex = int.Parse(startRowStr) - 1;
1743 }
1744
1745 int? endRowIndex;
1746 if (string.IsNullOrEmpty(endRowStr))
1747 {
1748 endRowIndex = null;
1749 }
1750 else
1751 {
1752 endRowIndex = int.Parse(endRowStr);
1753 }
1754
1755 int? startColumnIndex;
1756 if (string.IsNullOrEmpty(startCol))
1757 {
1758 startColumnIndex = null;
1759 }
1760 else
1761 {
1762 startColumnIndex = ColumnToIndex(startCol);
1763 }
1764
1765 int? endColumnIndex;
1766 if (string.IsNullOrEmpty(endCol))
1767 {
1768 endColumnIndex = null;
1769 }
1770 else
1771 {
1772 endColumnIndex = ColumnToIndex(endCol) + 1;
1773 }
1774
1775 return (sheetId, startRowIndex, endRowIndex, startColumnIndex, endColumnIndex);
1776 }
1777
1778 private int ColumnToIndex(string column)
1779 {
1780 int index = 0;
1781 foreach (char c in column.ToUpper())
1782 {
1783 index = index * 26 + (c - 'A' + 1);
1784 }
1785 return index - 1;
1786 }
1787
1788 public IAllowTriggerSettings WithTriggerFunction(string functionName)
1789 {
1790 if (string.IsNullOrEmpty(functionName))
1791 {
1792 GPAL.PublishSimpleEvent(GPALEventType.WARNING, "Trigger function name is empty", this, GPALObjectType.GoogleSheets);
1793 }
1794 FunctionName = functionName;
1795 return this;
1796 }
1797
1798 public IAllowTriggerSettings WithTriggerSchedule(TriggerScheduleType schedule)
1799 {
1800 TriggerSchedule = schedule;
1801 return this;
1802 }
1803
1804 public IAllowDataOperations CreateTrigger(GoogleTriggerType triggerType)
1805 {
1806 if (string.IsNullOrEmpty(AppsScriptProjectId))
1807 {
1808 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Apps Script project ID not set", this, GPALObjectType.GoogleSheets);
1809 return this;
1810 }
1811
1812 if (string.IsNullOrEmpty(FunctionName))
1813 {
1814 if (string.IsNullOrEmpty(ScriptName))
1815 {
1816 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Trigger function name not set", this, GPALObjectType.GoogleSheets);
1817 return this;
1818 }
1819 FunctionName = ScriptName;
1820 GPAL.PublishSimpleEvent(GPALEventType.WARNING, $"Using script name [{ScriptName}] as trigger function name", this, GPALObjectType.GoogleSheets);
1821 }
1822
1823 var triggerData = new Dictionary<string, object> { { "functionName", FunctionName } };
1824 if (triggerType == GoogleTriggerType.TimeBased && TriggerSchedule != TriggerScheduleType.None)
1825 {
1826 triggerData["timeBased"] = new { type = TriggerSchedule.ToString().ToUpper() };
1827 }
1828
1829 var response = ScriptsRESTClient
1830 .WithEndpoint(string.Format(googleSheetsConfig.TriggerEndpoint, AppsScriptProjectId))
1831 .WithParameters(triggerData)
1832 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1833 .Execute();
1834
1835 if (response == null)
1836 {
1837 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to create trigger", this, GPALObjectType.GoogleSheets);
1838 }
1839
1840 return this;
1841 }
1842
1843 public string SetupCloudProject(string projectName)
1844 {
1845 if (string.IsNullOrEmpty(projectName))
1846 {
1847 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Cloud project name cannot be empty", this, GPALObjectType.GoogleSheets);
1848 return "";
1849 }
1850
1851 if (Credentials == null)
1852 {
1853 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Credentials not set", this, GPALObjectType.GoogleSheets);
1854 return "";
1855 }
1856
1857 _googleCloud = new GoogleCloud(Credentials);
1858 string projectId = _googleCloud.CreateProject(projectName);
1859 if (string.IsNullOrEmpty(projectId))
1860 {
1861 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to create or retrieve cloud project", this, GPALObjectType.GoogleSheets);
1862 return "";
1863 }
1864
1865 bool sheetsEnabled = _googleCloud.EnableApi(projectId, GoogleApi.Sheets);
1866 bool scriptsEnabled = _googleCloud.EnableApi(projectId, GoogleApi.Scripts);
1867
1868 if (!sheetsEnabled || !scriptsEnabled)
1869 {
1870 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to enable Sheets or Scripts API", this, GPALObjectType.GoogleSheets);
1871 return "";
1872 }
1873
1874 return projectId;
1875 }
1876
1877 public string GetOrCreateScriptProject(string cloudProjectId, string scriptTitle)
1878 {
1879 if (string.IsNullOrEmpty(cloudProjectId) || string.IsNullOrEmpty(scriptTitle))
1880 {
1881 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Cloud project ID or script title cannot be empty", this, GPALObjectType.GoogleSheets);
1882 return "";
1883 }
1884
1885 if (Credentials == null)
1886 {
1887 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Credentials not set", this, GPALObjectType.GoogleSheets);
1888 return "";
1889 }
1890
1891 var listResponse = ScriptsRESTClient
1892 .WithEndpoint("/v1/projects")
1893 .WithParameters(new { pageSize = 50 })
1894 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1895 .Execute();
1896
1897 if (listResponse != null)
1898 {
1899 try
1900 {
1901 var listData = JsonSerializer.Deserialize<Dictionary<string, object>>(listResponse);
1902 object projectsObj = null;
1903 if (listData.TryGetValue("projects", out projectsObj))
1904 {
1905 var projects = JsonSerializer.Deserialize<JsonElement>(projectsObj.ToString());
1906 foreach (var project in projects.EnumerateArray())
1907 {
1908 if (project.TryGetProperty("title", out var title) && title.GetString().Equals(scriptTitle) &&
1909 project.TryGetProperty("scriptId", out var projectId))
1910 {
1911 return projectId.GetString();
1912 }
1913 }
1914 }
1915 }
1916 catch { }
1917 }
1918
1919 var scriptPayload = new { title = scriptTitle, parentId = cloudProjectId };
1920 var createResponse = ScriptsRESTClient
1921 .WithEndpoint("/v1/projects")
1922 .WithParameters(scriptPayload)
1923 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
1924 .Execute();
1925
1926 if (createResponse == null)
1927 {
1928 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to create Apps Script project", this, GPALObjectType.GoogleSheets);
1929 return "";
1930 }
1931
1932 try
1933 {
1934 var scriptData = JsonSerializer.Deserialize<Dictionary<string, object>>(createResponse);
1935 object idObj = null;
1936 if (scriptData.TryGetValue("scriptId", out idObj))
1937 {
1938 return idObj.ToString();
1939 }
1940 }
1941 catch { }
1942
1943 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No Apps Script project ID returned", this, GPALObjectType.GoogleSheets);
1944 return "";
1945 }
1946
1947 public IAllowScriptOperations WithProjectId(string projectId)
1948 {
1949 if (string.IsNullOrEmpty(projectId))
1950 {
1951 GPAL.PublishSimpleEvent(GPALEventType.WARNING, "Apps Script project ID is empty", this, GPALObjectType.GoogleSheets);
1952 }
1953 AppsScriptProjectId = projectId;
1954 return this;
1955 }
1956
1957 public IAllowScriptOperations WithScriptName(string scriptName)
1958 {
1959 if (string.IsNullOrEmpty(scriptName))
1960 {
1961 GPAL.PublishSimpleEvent(GPALEventType.WARNING, "Script name is empty", this, GPALObjectType.GoogleSheets);
1962 }
1963 ScriptName = scriptName;
1964 return this;
1965 }
1966
1967 public IAllowScriptOperations WithScriptAccess(ScriptAccessType access)
1968 {
1969 ScriptAccess = access;
1970 return this;
1971 }
1972
1973 public IAllowScriptOperations WithExecuteAs(ExecuteAsType executeAs)
1974 {
1975 ExecuteAs = executeAs;
1976 return this;
1977 }
1978
1979 public IAllowScriptOperations WithDeploymentDescription(string description)
1980 {
1981 if (string.IsNullOrEmpty(description))
1982 {
1983 GPAL.PublishSimpleEvent(GPALEventType.WARNING, "Deployment description is empty; using default", this, GPALObjectType.GoogleSheets);
1984 DeploymentDescription = "Web app deployment for script";
1985 }
1986 else
1987 {
1988 DeploymentDescription = description;
1989 }
1990 return this;
1991 }
1992
1993 public IAllowScriptOperations WithVersionNumber(int versionNumber)
1994 {
1995 if (versionNumber < 1)
1996 {
1997 GPAL.PublishSimpleEvent(GPALEventType.WARNING, "Version number must be positive; using default 1", this, GPALObjectType.GoogleSheets);
1998 VersionNumber = 1;
1999 }
2000 else
2001 {
2002 VersionNumber = versionNumber;
2003 }
2004 return this;
2005 }
2006
2007 public IAllowDeployWebApp UploadScript(string scriptContent)
2008 {
2009 if (string.IsNullOrEmpty(scriptContent))
2010 {
2011 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Script content cannot be empty", this, GPALObjectType.GoogleSheets);
2012 return this;
2013 }
2014
2015 if (string.IsNullOrEmpty(AppsScriptProjectId))
2016 {
2017 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Apps Script project ID not set", this, GPALObjectType.GoogleSheets);
2018 return this;
2019 }
2020
2021 if (Credentials == null)
2022 {
2023 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Credentials not set", this, GPALObjectType.GoogleSheets);
2024 return this;
2025 }
2026
2027 if (string.IsNullOrEmpty(ScriptName))
2028 {
2029 ScriptName = "main";
2030 GPAL.PublishSimpleEvent(GPALEventType.INFO, "Using default script name 'main'", this, GPALObjectType.GoogleSheets);
2031 }
2032
2033 var response = ScriptsRESTClient
2034 .WithEndpoint(string.Format(googleSheetsConfig.ScriptContentEndpoint, AppsScriptProjectId))
2035 .WithParameters(new
2036 {
2037 files = new[] { new { name = ScriptName, type = googleSheetsConfig.ScriptFileType, source = scriptContent } }
2038 })
2039 .WithHttpMethod("PUT")
2040 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
2041 .Execute();
2042
2043 if (response == null)
2044 {
2045 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to upload script", this, GPALObjectType.GoogleSheets);
2046 }
2047 else
2048 {
2049 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Script uploaded to project [{AppsScriptProjectId}]", this, GPALObjectType.GoogleSheets);
2050 }
2051
2052 return this;
2053 }
2054
2055 public IAllowDeployWebApp UploadScript(GPALFile scriptFile)
2056 {
2057 if (scriptFile == null)
2058 {
2059 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Script file is null", this, GPALObjectType.GoogleSheets);
2060 return this;
2061 }
2062
2063 string scriptContent = "";
2064 try
2065 {
2066 scriptContent = File.ReadAllText(scriptFile.Filename);
2067 }
2068 catch (Exception ex)
2069 {
2070 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Failed to read script file", this, GPALObjectType.GoogleSheets, ex);
2071 return this;
2072 }
2073
2074 return UploadScript(scriptContent);
2075 }
2076
2077 public IAllowDataOperations DeployScriptAsWebApp(out string webAppUrl)
2078 {
2079 webAppUrl = "";
2080 if (string.IsNullOrEmpty(AppsScriptProjectId))
2081 {
2082 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Apps Script project ID not set", this, GPALObjectType.GoogleSheets);
2083 return this;
2084 }
2085
2086 if (Credentials == null)
2087 {
2088 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Credentials not set", this, GPALObjectType.GoogleSheets);
2089 return this;
2090 }
2091
2092 if (ExecuteAs == ExecuteAsType.USER_ACCESSING && ScriptAccess != ScriptAccessType.ANYONE && ScriptAccess != ScriptAccessType.DOMAIN)
2093 {
2094 GPAL.PublishSimpleEvent(GPALEventType.WARNING, "ExecuteAs 'USER_ACCESSING' requires access 'ANYONE' or 'DOMAIN'; using 'USER_DEPLOYING'", this, GPALObjectType.GoogleSheets);
2095 ExecuteAs = ExecuteAsType.USER_DEPLOYING;
2096 }
2097
2098 // First, create a version
2099 var versionPayload = new { description = "Version for deployment" };
2100 var versionResponse = ScriptsRESTClient
2101 .WithEndpoint($"/v1/projects/{AppsScriptProjectId}/versions")
2102 .WithParameters(versionPayload)
2103 .WithHttpMethod("POST")
2104 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
2105 .Execute();
2106
2107 if (versionResponse == null)
2108 {
2109 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to create script version", this, GPALObjectType.GoogleSheets);
2110 return this;
2111 }
2112
2113 try
2114 {
2115 var versionData = JsonSerializer.Deserialize<Dictionary<string, object>>(versionResponse);
2116 object versionObj = null;
2117 if (versionData.TryGetValue("versionNumber", out versionObj))
2118 {
2119 VersionNumber = Convert.ToInt32(versionObj);
2120 }
2121 else
2122 {
2123 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No version number returned", this, GPALObjectType.GoogleSheets);
2124 return this;
2125 }
2126 }
2127 catch
2128 {
2129 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to parse version response", this, GPALObjectType.GoogleSheets);
2130 return this;
2131 }
2132
2133 string accessString = ScriptAccess switch
2134 {
2135 ScriptAccessType.ME => "ME",
2136 ScriptAccessType.ANYONE => "ANYONE",
2137 ScriptAccessType.ANYONE_ANONYMOUS => "ANYONE_ANONYMOUS",
2138 ScriptAccessType.DOMAIN => "DOMAIN",
2139 _ => "ME"
2140 };
2141
2142 string executeAsString = ExecuteAs switch
2143 {
2144 ExecuteAsType.USER_ACCESSING => "USER_ACCESSING",
2145 ExecuteAsType.USER_DEPLOYING => "USER_DEPLOYING",
2146 _ => "USER_DEPLOYING"
2147 };
2148
2149 var deploymentPayload = new
2150 {
2151 versionNumber = VersionNumber,
2152 manifestFileName = "appsscript",
2153 description = DeploymentDescription
2154 };
2155
2156 var deployResponse = ScriptsRESTClient
2157 .WithEndpoint($"/v1/projects/{AppsScriptProjectId}/deployments")
2158 .WithParameters(deploymentPayload)
2159 .WithHttpMethod("POST")
2160 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
2161 .Execute();
2162
2163 if (deployResponse == null)
2164 {
2165 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to deploy script", this, GPALObjectType.GoogleSheets);
2166 return this;
2167 }
2168
2169 try
2170 {
2171 var deployData = JsonSerializer.Deserialize<Dictionary<string, object>>(deployResponse);
2172 object deploymentIdObj = null;
2173 if (!deployData.TryGetValue("deploymentId", out deploymentIdObj))
2174 {
2175 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No deployment ID returned", this, GPALObjectType.GoogleSheets);
2176 return this;
2177 }
2178
2179 string deploymentId = deploymentIdObj.ToString();
2180 var updatePayload = new
2181 {
2182 deploymentConfig = new
2183 {
2184 scriptId = AppsScriptProjectId,
2185 versionNumber = VersionNumber,
2186 manifestFileName = "appsscript",
2187 description = DeploymentDescription,
2188 webApp = new { executeAs = executeAsString, whoHasAccess = accessString }
2189 }
2190 };
2191
2192 var updateResponse = ScriptsRESTClient
2193 .WithEndpoint($"/v1/projects/{AppsScriptProjectId}/deployments/{deploymentId}")
2194 .WithParameters(updatePayload)
2195 .WithHttpMethod("PUT")
2196 .WithHeader(googleSheetsConfig.AuthorizationHeader, $"{googleSheetsConfig.Bearer} {((Credentials)Credentials)?.AccessToken ?? ""}")
2197 .Execute();
2198
2199 if (updateResponse == null)
2200 {
2201 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to update deployment", this, GPALObjectType.GoogleSheets);
2202 return this;
2203 }
2204
2205 var updateData = JsonSerializer.Deserialize<Dictionary<string, object>>(updateResponse);
2206 object entryPointsObj = null;
2207 if (updateData.TryGetValue("entryPoints", out entryPointsObj))
2208 {
2209 var entryPoints = JsonSerializer.Deserialize<JsonElement>(entryPointsObj.ToString());
2210 foreach (var entry in entryPoints.EnumerateArray())
2211 {
2212 if (entry.TryGetProperty("webApp", out var webApp) && webApp.TryGetProperty("url", out var url))
2213 {
2214 webAppUrl = url.GetString();
2215 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Web app deployed at: [{webAppUrl}]", this, GPALObjectType.GoogleSheets);
2216 return this;
2217 }
2218 }
2219 }
2220 }
2221 catch (Exception ex)
2222 {
2223 GPAL.PublishSimpleEvent(GPALEventType.ERROR, $"Deployment error", this, GPALObjectType.GoogleSheets, ex);
2224 }
2225
2226 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "Failed to retrieve web app URL", this, GPALObjectType.GoogleSheets);
2227 return this;
2228 }
2229
2230 public IAllowSheetSelection SaveTo(GPALFile gPALFile)
2231 {
2232 if (gPALFile == null)
2233 {
2234 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "GPAL file is null", this, GPALObjectType.GoogleSheets);
2235 return this;
2236 }
2237
2238 if (ReadData == null)
2239 {
2240 if (!string.IsNullOrEmpty(ReadRange))
2241 {
2242 if (!ReadDataFromRange())
2243 {
2244 return this;
2245 }
2246 }
2247 else
2248 {
2249 GPAL.PublishSimpleEvent(GPALEventType.ERROR, "No data to save; ReadData is null and no range specified", this, GPALObjectType.GoogleSheets);
2250 return this;
2251 }
2252 }
2253
2254 GPAL.PublishSimpleEvent(GPALEventType.INFO, $"Saving [{ReadData.Rows}] rows of sheet [{SheetName}] to [{gPALFile.Filename}].", this, GPALObjectType.GoogleSheets);
2255
2256 try
2257 {
2258 using (var writer = new StreamWriter(gPALFile.Filename))
2259 {
2260 foreach (var row in ReadData)
2261 {
2262 writer.WriteLine(string.Join(",", row));
2263 }
2264 }
2265 }
2266 catch (Exception ex)
2267 {
2268 GPAL.PublishSimpleEvent(GPALEventType.EXCEPTION, $"Failed to save to file", this, GPALObjectType.GoogleSheets, ex);
2269 }
2270
2271 return this;
2272 }
2273
2274 public IGoogleSheets ToGPALObject()
2275 {
2276 return this;
2277 }
2278 }
2279
2280 internal class ValueRange
2281 {
2282 public string range { get; set; }
2283 public string majorDimension { get; set; }
2284 public List<List<object>> values { get; set; }
2285 }
2286
2287 internal class SpreadsheetResponse
2288 {
2289 public string SpreadsheetId { get; set; }
2290 public List<Sheet> Sheets { get; set; }
2291
2292 public class Sheet
2293 {
2294 public SheetProperties Properties { get; set; }
2295 }
2296
2297 public class SheetProperties
2298 {
2299 public string Title { get; set; }
2300 public int SheetId { get; set; }
2301 }
2302 }
2303}
GenerallyPositive.Credentials.FetchAccessToken
void FetchAccessToken(out string accessToken)
Fetches a new access token for ServiceType using the credentials/configuration supplied so far,...
Definition Credentials.cs:1223
GenerallyPositive.GPALException
Thrown where GPAL deliberately ends the workflow, such as a CallIf handler returning CallIfStatus....
Definition GPALException.cs:28
GenerallyPositive.GPALFile.Filename
string Filename
We have only one file, accessing it.
Definition GPALFile.cs:474
GenerallyPositive.GPAL
Everything starts here, all the GPAL controls and global settings using fluent syntax are here....
Definition GPAL.cs:49
GenerallyPositive.GPAL.RESTClient
static IAllowRESTEndpoint RESTClient
Instantiate a new fluent RESTClient.
Definition GPAL.cs:914
GenerallyPositive.GPAL.PublishSimpleEvent
static void PublishSimpleEvent(GPALEventType gPALEventType, string msg, dynamic gPALObject=null, Enums.GPALObjectType gPALObjectType=GPALObjectType.None, Exception ex=null)
Publish a message to either the information channel or exception channel (if exception passed in) Pub...
Definition GPAL.cs:2406
GenerallyPositive.GoogleCloud
Handles Google Cloud operations such as project creation, API enabling, and API key generation.
Definition GoogleCloud.cs:30
GenerallyPositive.GoogleSheets.WithWebAppUrl
IAllowSpreadsheetSelection WithWebAppUrl(string webAppUrl)
Drives sheets through an Apps Script web app that was deployed by hand, rather than through the Sheet...
Definition GoogleSheets.cs:826