(SQL Server) Microsoft Teams - Create Team (complex request)

Create a team with multiple channels, installed apps, and pinned tabs using delegated permissions

For more information, see https://docs.microsoft.com/en-us/graph/api/team-post?view=graph-rest-1.0&tabs=http#example-3-create-a-team-with-multiple-channels-installed-apps-and-pinned-tabs-using-delegated-permissions

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Build the following HTTP request:

    -- POST https://graph.microsoft.com/v1.0/teams
    -- Content-Type: application/json
    -- 
    -- {
    --     "template@odata.bind": "https://graph.microsoft.com/v1.0/teamsTemplates('standard')",
    --     "visibility": "Private",
    --     "displayName": "Sample Engineering Team",
    --     "description": "This is a sample engineering team, used to showcase the range of properties supported by this API",
    --     "channels": [
    --         {
    --             "displayName": "Announcements",
    --             "isFavoriteByDefault": true,
    --             "description": "This is a sample announcements channel that is favorited by default. Use this channel to make important team, product, and service announcements."
    --         },
    --         {
    --             "displayName": "Training",
    --             "isFavoriteByDefault": true,
    --             "description": "This is a sample training channel, that is favorited by default, and contains an example of pinned website and YouTube tabs.",
    --             "tabs": [
    --                 {
    --                     "teamsApp@odata.bind": "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps('com.microsoft.teamspace.tab.web')",
    --                     "name": "A Pinned Website",
    --                     "configuration": {
    --                         "contentUrl": "https://docs.microsoft.com/microsoftteams/microsoft-teams"
    --                     }
    --                 },
    --                 {
    --                     "teamsApp@odata.bind": "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps('com.microsoft.teamspace.tab.youtube')",
    --                     "name": "A Pinned YouTube Video",
    --                     "configuration": {
    --                         "contentUrl": "https://tabs.teams.microsoft.com/Youtube/Home/YoutubeTab?videoId=X8krAMdGvCQ",
    --                         "websiteUrl": "https://www.youtube.com/watch?v=X8krAMdGvCQ"
    --                     }
    --                 }
    --             ]
    --         },
    --         {
    --             "displayName": "Planning",
    --             "description": "This is a sample of a channel that is not favorited by default, these channels will appear in the more channels overflow menu.",
    --             "isFavoriteByDefault": false
    --         },
    --         {
    --             "displayName": "Issues and Feedback",
    --             "description": "This is a sample of a channel that is not favorited by default, these channels will appear in the more channels overflow menu."
    --         }
    --     ],
    --     "memberSettings": {
    --         "allowCreateUpdateChannels": true,
    --         "allowDeleteChannels": true,
    --         "allowAddRemoveApps": true,
    --         "allowCreateUpdateRemoveTabs": true,
    --         "allowCreateUpdateRemoveConnectors": true
    --     },
    --     "guestSettings": {
    --         "allowCreateUpdateChannels": false,
    --         "allowDeleteChannels": false
    --     },
    --     "funSettings": {
    --         "allowGiphy": true,
    --         "giphyContentRating": "Moderate",
    --         "allowStickersAndMemes": true,
    --         "allowCustomMemes": true
    --     },
    --     "messagingSettings": {
    --         "allowUserEditMessages": true,
    --         "allowUserDeleteMessages": true,
    --         "allowOwnerDeleteMessages": true,
    --         "allowTeamMentions": true,
    --         "allowChannelMentions": true
    --     },
    --     "discoverySettings": {
    --         "showInTeamsSearchAndSuggestions": true
    --     },
    --     "installedApps": [
    --         {
    --             "teamsApp@odata.bind": "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps('com.microsoft.teamspace.tab.vsts')"
    --         },
    --         {
    --             "teamsApp@odata.bind": "https://graph.microsoft.com/v1.0/appCatalogs/teamsApps('1542629c-01b3-4a6d-8f76-1938b779e48d')"
    --         }
    --     ]
    -- }

    -- Use this online tool to generate code from sample JSON:
    -- Generate Code to Create JSON

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, '"template@odata.bind"', 'https://graph.microsoft.com/v1.0/teamsTemplates(''standard'')'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'visibility', 'Private'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'displayName', 'Sample Engineering Team'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'description', 'This is a sample engineering team, used to showcase the range of properties supported by this API'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[0].displayName', 'Announcements'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'channels[0].isFavoriteByDefault', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[0].description', 'This is a sample announcements channel that is favorited by default. Use this channel to make important team, product, and service announcements.'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].displayName', 'Training'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'channels[1].isFavoriteByDefault', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].description', 'This is a sample training channel, that is favorited by default, and contains an example of pinned website and YouTube tabs.'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[0]."teamsApp@odata.bind"', 'https://graph.microsoft.com/v1.0/appCatalogs/teamsApps(''com.microsoft.teamspace.tab.web'')'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[0].name', 'A Pinned Website'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[0].configuration.contentUrl', 'https://docs.microsoft.com/microsoftteams/microsoft-teams'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[1]."teamsApp@odata.bind"', 'https://graph.microsoft.com/v1.0/appCatalogs/teamsApps(''com.microsoft.teamspace.tab.youtube'')'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[1].name', 'A Pinned YouTube Video'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[1].configuration.contentUrl', 'https://tabs.teams.microsoft.com/Youtube/Home/YoutubeTab?videoId=X8krAMdGvCQ'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[1].tabs[1].configuration.websiteUrl', 'https://www.youtube.com/watch?v=X8krAMdGvCQ'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[2].displayName', 'Planning'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[2].description', 'This is a sample of a channel that is not favorited by default, these channels will appear in the more channels overflow menu.'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'channels[2].isFavoriteByDefault', 0
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[3].displayName', 'Issues and Feedback'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'channels[3].description', 'This is a sample of a channel that is not favorited by default, these channels will appear in the more channels overflow menu.'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowCreateUpdateChannels', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowDeleteChannels', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowAddRemoveApps', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowCreateUpdateRemoveTabs', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'memberSettings.allowCreateUpdateRemoveConnectors', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'guestSettings.allowCreateUpdateChannels', 0
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'guestSettings.allowDeleteChannels', 0
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'funSettings.allowGiphy', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'funSettings.giphyContentRating', 'Moderate'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'funSettings.allowStickersAndMemes', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'funSettings.allowCustomMemes', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowUserEditMessages', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowUserDeleteMessages', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowOwnerDeleteMessages', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowTeamMentions', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'messagingSettings.allowChannelMentions', 1
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'discoverySettings.showInTeamsSearchAndSuggestions', 1
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'installedApps[0]."teamsApp@odata.bind"', 'https://graph.microsoft.com/v1.0/appCatalogs/teamsApps(''com.microsoft.teamspace.tab.vsts'')'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'installedApps[1]."teamsApp@odata.bind"', 'https://graph.microsoft.com/v1.0/appCatalogs/teamsApps(''1542629c-01b3-4a6d-8f76-1938b779e48d'')'

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-type', 'application/json'

    -- Adds the "Authorization: Bearer ACCESS_TOKEN" header.
    EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://graph.microsoft.com/v1.0/teams', 'application/json', @json
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    -- A successful response is indicated by a 202 response status code and an empty response body.

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    PRINT 'Response Status Code: ' + @iTmp0

    PRINT 'Response Body:'
    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    PRINT @sTmp0

    EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
    IF @iTmp0 >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
      END

    EXEC @hr = sp_OADestroy @resp


    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json


END
GO