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