'스프레드시트'에 해당되는 글 2건

• 저장용

using System.Linq;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using UnityEngine.Networking;
using Newtonsoft.Json;

public class ModuleDataDownloadTable : MonoBehaviour
{
    // Constants used for API call
    private const string KeyTable = "여기에 전체 공유된 구글 스프레드시트 번호 넣기";
    private const string KeyAuth = "여기에 구글 스프레드시트 인증 넣기(참고: https://ajh322.tistory.com/247)";
    private const string UrlFormat = "https://sheets.googleapis.com/v4/spreadsheets/{0}/values/{1}?key={2}";

    // Initialization method
    public void Init()
    {
    }

    // Coroutine for downloading table data
    public IEnumerator DownloadTable(string tableName, System.Action<string> afterAction)
    {
        Debug.LogWarning($":: Download Table : {tableName} Start");

        // Create a new web request with the appropriate URL
        using UnityWebRequest req = UnityWebRequest.Get(PickURL(tableName));
        yield return req.SendWebRequest();

        // Handle potential request errors
        if (req.result == UnityWebRequest.Result.ConnectionError || req.result == UnityWebRequest.Result.ProtocolError)
        {
            Debug.LogError(req.error);
            yield break;
        }

        // Deserialize the response and convert to JSON
        string[][] values = DeserializeResponse(req.downloadHandler.text);
        afterAction(ConvertValuesToJson(values));

        Debug.Log($":: Download Table : {tableName} Complete");
    }

    // Deserialize the HTTP response
    private string[][] DeserializeResponse(string response)
    {
        return JsonConvert.DeserializeObject<GoogleSheetClass>(response).values;
    }

    // Convert the data values to a JSON string
    private string ConvertValuesToJson(string[][] values)
    {
        Dictionary<int, string> keys = new();
        List<string> jsonObjects = new List<string>();

        for (int i = 0; i < values.Length; i++)
        {
            if (i == 0)
            {
                // Set keys from the first row of values
                SetKeys(keys, values[i]);
                continue;
            }

            // Create JSON objects from subsequent rows of values
            jsonObjects.Add(GenerateJsonObject(keys, values[i]));
        }

        return $"[{string.Join(",", jsonObjects)}]";
    }

    // Set keys for JSON objects from the first row of data
    private void SetKeys(Dictionary<int, string> keys, string[] row)
    {
        for (int i = 0; i < row.Length; i++)
        {
            keys.Add(i, row[i]);
        }
    }

    // Generate a JSON object as a string
    private string GenerateJsonObject(Dictionary<int, string> keys, string[] row)
    {
        List<string> properties = new List<string>();

        for (int i = 0; i < row.Length; i++)
        {
            if (keys[i].Contains('~')) continue;

            properties.Add($"\"{keys[i]}\":\"{row[i]}\"");
        }

        return $"{{{string.Join(",", properties)}}}";
    }

    // Form URL for HTTP request
    private string PickURL(string tableName)
    {
        return string.Format(UrlFormat, KeyTable, tableName, KeyAuth);
    }

    // Inner class to match the structure of the HTTP response for deserialization
    private class GoogleSheetClass
    {
        public string range { get; set; }
        public string majorDimension { get; set; }
        public string[][] values { get; set; }
    }
}

#region Download Table
    public bool IsTableDownloaded { get; private set; } = false;
    private async void DownloadTable()
    {
        IsTableDownloaded = false;

        this._moduleDownloadTable = this.gameObject.AddComponent<ModuleDataDownloadTable>();

        var task1 = InitializeData<DataText>(Constants.PATH_DATA_TEXT, (texts) =>
        {
            _texts = texts.ToDictionary(ele => ele.idx);
        });

        var task2 = InitializeData<DataCard>(Constants.PATH_DATA_CARD, (cards) =>
        {
            _cards = cards.ToDictionary(ele => ele.idx);
            _cardsGroup = cards.GroupBy(ele => ele.group).ToDictionary(ele => ele.Key, ele => ele.ToArray());
        });

        var task3 = InitializeData<DataCardDropGroup>(Constants.PATH_DATA_CARD_DROP_GROUP, (cardDropGroups) =>
        {
            _cardDropGroup = cardDropGroups.GroupBy(ele => ele.group_idx).ToDictionary(ele => ele.Key, ele => ele.ToArray());
        });

        var task4 = InitializeData<DataCardBuyGroup>(Constants.PATH_DATA_CARD_BUY_GROUP, (cardBuyGroups) =>
        {
            _cardBuyGroup = cardBuyGroups.GroupBy(ele => ele.group_idx).ToDictionary(ele => ele.Key, ele => ele.ToArray());
        });

        await Task.WhenAll(task1, task2, task3, task4);

        IsTableDownloaded = true;
    }
    async Task InitializeData<T>(string path, System.Action<T[]> afterDeserialize)
    {
        string cleanedPath = path.Replace("Data/", "");
        var deserializedData = await Deserialize<T>(cleanedPath);
        afterDeserialize(deserializedData);
    }
    private async Task<T[]> Deserialize<T>(string path)
    {
        // Create a task completion source.
        TaskCompletionSource<T[]> tcs = new TaskCompletionSource<T[]>();

        // Start the download table task.
        StartCoroutine(_moduleDownloadTable.DownloadTable(path, (string json) =>
        {
            // Deserialize the json data to the specific object type array.
            T[] result = JsonConvert.DeserializeObject<T[]>(json);

            // Set the result to the task completion source.
            tcs.SetResult(result);
        }));

        // Await for the task to be completed.
        T[] data = await tcs.Task;

        // Return the deserialized data.
        return data;
    }
    #endregion

사용법

• =REGEXREPLACE([변경할 곳],"\([^)]*\)","")
  
  • 설명: 정규식으로 괄호 내용을 찾아서 빈 것으로 교체
  • 괄호 찾는 정규식: \([^)]*\)