• Registered: 2014-02-17
  • Posts: 391

Topic: Example Get Value From Google Sheet Public Is Json

Google Sheet Public >>> Get >>> Response Json


Referent
- Example Google Sheet : https://docs.google.com/spreadsheets/d/ … id=0#gid=0
- Reference How to use Google Sheets as a JSON Endpoint : https://www.freecodecamp.org/news/cjn-g … -endpoint/
- Basic Structure : https://docs.google.com/spreadsheets/d/[GOOGLE_SHEET_ID]/gviz/tq?tqx=out:json&tq&gid=[SHEET_GID]
- Google Visualization API Query Language : https://developers.google.com/chart/int … rylanguage

Example [QUERY_STRING] : tq
- Select All : Where+1=1
- Student Name = Anna : Where+A='Anna'
- Gender = Female : Where+B='Female'


https://i.ibb.co/7xXGVcfC/D20250314-T104645.png

Post's attachments

Attachment icon GSheetJson.zip 5.92 kb, 50 downloads since 2025-03-14 

My Visual Database : I Love You
Easy For Beginner Student For Me
  • Registered: 2024-11-27
  • Posts: 33

Re: Example Get Value From Google Sheet Public Is Json

Great idea... but can the project be improved?
1- Can the data be displayed in a grid table?
2- Can the data be modified using MVD and automatically modified in Google Sheets?

3 Reply by prahousefamily (edited by prahousefamily 2025-03-18 02:28:48)

  • Registered: 2014-02-17
  • Posts: 391

Re: Example Get Value From Google Sheet Public Is Json

abdou88 wrote:

Great idea... but can the project be improved?
1- Can the data be displayed in a grid table?

Processing, please wait. Creating a function to convert json to a table

My Visual Database : I Love You
Easy For Beginner Student For Me

4 Reply by prahousefamily (edited by prahousefamily 2025-03-19 03:39:31)

  • Registered: 2014-02-17
  • Posts: 391

Re: Example Get Value From Google Sheet Public Is Json

+ Export Type
tqx=out:html: Export HTML
tqx=out:csv: Export CSV (Comma-Separated Values)
tqx=out:tsv: Export TSV (Tab-Separated Values)
tqx=out:tsv: Export Json (JavaScript Object Notation)

https://i.ibb.co/jvmkMYZc/D20250319-T102209.png


var
WinHttpReq : Variant;
procedure Form1_Button2_OnClick (Sender: TObject; var Cancel: boolean);
var
url : string ;
ResponseText: string;
sl : TStringlist ;
Begin

    url := Form1.Edit4.Text ;
    Form1.Memo1.Lines.Clear ;
    WinHttpReq.SetTimeouts(10000, 10000, 10000, 10000);
    WinHttpReq.Open('GET',url, True);
    Try
        WinHttpReq.Send;
        WinHttpReq.WaitForResponse ;
        ResponseText := WinHttpReq.ResponseText;
        sl := TStringlist.Create ;
        sl.Text :=  ResponseText ;
        Form1.Memo1.Text := sl.text ;
        sl.Free;
        Except
        Form1.Memo1.Clear ;
        Showmessage('Error');
    End;
end;
procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
url : string ;
gid : String ;
tqx : String ;
tq  : String ;
address : String ;
begin
    url :=  Form1.Edit1.Text ;
    gid :=  Form1.Edit2.Text ;
    tqx :=  Form1.ComboBox1.Text ;
    tq  :=  Form1.Edit3.Text ;
    address :=
    'https://docs.google.com/spreadsheets/d/'+url+
    '/gviz/tq?tqx='+tqx+
    '&gid='+gid+
    '&tq='+tq ;
    Form1.Edit4.Text :=  address ;
end;
begin
    WinHttpReq := CreateOleObject('WinHttp.WinHttpRequest.5.1');
    Form1.ComboBox1.Items.Add('out:html');
    Form1.ComboBox1.Items.Add('out:csv');
    Form1.ComboBox1.Items.Add('out:tsv');
    Form1.ComboBox1.Items.Add('out:json');
    Form1.ComboBox1.ItemIndex:= 0;
end.
Post's attachments

Attachment icon GGSheetView.zip 4.87 kb, 44 downloads since 2025-03-19 

My Visual Database : I Love You
Easy For Beginner Student For Me