SharePoint Online Connector

SELECT * FROM Lists

SELECT * FROM MyList 
--WHERE Id = 1234

INSERT INTO MyList(Name, Surname)
VALUES ('John', 'Doe') WITH (Output=1)

INSERT INTO MyList(Title, ManyPersonsLookupId, "fields.ManyPersonsLookupId@odata\u002Etype")
VALUES ('John Doe', '[11,22,33]', 'Collection(Edm.Int32)')
WITH (Output=1)
--Considering "ManyPersons" is the name of the field in SharePoint List

UPDATE MyList
SET Name = 'Elizabeth', Surname = 'Johnson'
WHERE Id = '123'

UPDATE MyList
SET Title = 'Elizabeth Johnson',
    ManyPersonsLookupId = '[11,22,33]',
    "fields.ManyPersonsLookupId@odata\u002Etype" = 'Collection(Edm.Int32)'
WHERE Id = '123'

DELETE FROM MyList WHERE Id=888
--OR--        
DELETE FROM MyList WITH (ListItemId='123')

--ListItemId can be retrieved by selecting from named list table, e.g. 'MyList'.

SELECT * FROM get_main_site

SELECT * FROM Sites

SELECT * FROM Drives

SELECT * FROM get_drive
WITH (DriveId='b!GtLQTMU726LE0eY5F2BBNi14-XXXXXXXXXXX-GuQ4DORpHy-XXXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM list_folders
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM list_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM list_excel_files
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXX')
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM get_list_fields
WITH (ListId='2e1d58e4-eced-4d1c-9279-XXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.

SELECT * FROM get_list_items
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.

SELECT * FROM get_list_items_dynamic
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX')
--You can get ListId by selecting from 'list_lists' endpoint.

INSERT INTO create_list_item(Title, OrderId)
VALUES ('My super title', 12345)
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
     ,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.

UPDATE update_list_item
SET Title = 'My super title', OrderId = 12345
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXX'
     ,ListItemId='775'
     ,Output=1)
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.

DELETE FROM delete_list_item
WITH (ListId='14bdfd1d-1090-4cfe-adc1-XXXXXXXXXXXXXX'
     ,ListItemId='775')
--You can get ListId by selecting from 'list_lists' endpoint.
--ListItemId can be retrieved by selecting from 'get_list_items' endpoint.

SELECT * FROM list_excel_worksheets
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXXX'
     ,FileId='01SUOJPKECYDDVFZWXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.

SELECT * FROM get_excel_worksheet_autodetect
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
  	    ,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
	    ,SheetId='Sheet1' --Id or Name
	    ,AutoDetectByValue='true'
		,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
		)
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.

SELECT * FROM get_excel_worksheet
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
  	 ,FileId='01SUOJPKECYDDVFXXXXXXXXXXXXXXXXXX'
	   ,SheetId='Sheet1' --Id or Name
	   ,Range='A1:K10000'
	   ,ArrayTransEnableCustomColumns='True' --set this to False if No header in first row
	   )
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.
--SheetId can be retrieved by downloading Excel file and discovering what sheets exist or by selecting from 'list_excel_worksheets' endpoint.
--Range should be set to Excel-type of range where data is located.

SELECT * FROM upload_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXX'
	 ,DiskFilePath='C:\My files\Employees\List.xls'
	 ,SharePointFilePath='Uploads/Employees/List.xls')
--DriveId can be retrieved by selecting from 'Drives' table.

SELECT * FROM download_file_to_disk
    WITH(
    DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMxxxxxxxxxxxxx'
    , FileId='01SUOJPKDXTQL2XI2EIFDYZxxxxxxxxxxxxx'
    , TargetFilePath='C:\temp\List.xlsx'
    --, FileOverwriteMode='FailIfExists' //change default overwrite mode from AlwaysOverwrite to FailIfExists
    )
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.

SELECT Data as BYTES_AS_BASE64_STRING, BASE64_TO_FILE('C:\My files\Employees\List.xls', Data) FILE_SAVED_TO_DISK_FROM_BASE64 
FROM download_file
WITH (DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-XXXXXXXXXXXXX'
	 ,FileId='01SUOJPKHXMPKD2UXXXXXXXXXXXXXXXXXX')
--DriveId can be retrieved by selecting from 'Drives' table.
--FileId can be retrieved by selecting from 'list_files' endpoint.

SELECT *,(JSON_VALUE(MyImageField,'$.serverUrl') || JSON_VALUE(MyImageField,'$.serverRelativeUrl')) as DevicePhotoUrl,
    FROM "My SharePoint List Name"

SELECT * FROM list_files
WITH(
     Filter='$.value[*]' --list both files and folders
   --Filter='$.value[?(@.file.mimeType != null)]' --list only files
   --Filter='$.value[?(@.folder.childCount != null)]' --list only folders
	,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
  --,SearchPath='/root:'	--default
  --,SearchPath='/root:/myfolder:'	--folder path
	,SearchPath='/root:/myfolder/subfolder:' --folder path nested
    ,OrderBy='lastModifiedDateTime desc'
)		
--You can get DriveId by selecting from 'Drives' table.

SELECT * FROM list_files
WITH(
     Filter='$.value[*]' --list both files and folders
   --Filter='$.value[?(@.file.mimeType != null)]' --list only files
   --Filter='$.value[?(@.folder.childCount != null)]' --list only folders
	,DriveId='b!GtLN726LE0eY5F2BBNi14wMKmwdpCDFMn1d71ra11GuQ4DORpHy-Sa5UzSpkaY9E'
	,SearchType='children' --must use this type if you like to list items by FolderId
    ,SearchPath='/items/01SUOJPKBVT5ZC2KVSXJAYSTTMA4I3GMHX'
)		
--You can get DriveId by selecting from 'Drives' table.
.