Scenario:
there is a new SP sub-folder every day with some files.
this needs to be shared to about 40 different emails daily.
BUT with 2 exclusions:
1. sometimes we want these 35 emails, sometimes some other 38 emails. so every day we exclude a few emails, every day different ones.
2. we do not want the "clients" to know about one another, so no bulk sharing or just external link, only share 1 by 1.
SOLUTION:
1. SPList for emails ("clients")
2. SP Library containing "main" folder, inside we create daily SP sub-folder with a name (usualy just the date like "25.8.23")
3. SPList for trigger the flow (power automate) on item created, getting from+archive for daily broadcast
4. flow for filtering out excluded emails, and sending 1 by 1 a share email.
lets go
1. SPList for emails ("clients")
really nothing other than new list, Title for "client name" and another text field for email
2. SP Library containing "main" folder, inside we create daily SP sub-folder with a name (usualy just the date like "25.8.23")
as stated
3. SPList for trigger the flow (power automate) on item created, getting from+archive for daily broadcast
new list, fields:
1. Title (is meaningless)
2. "exclude_emails" is multi-lookup for SPList of emails above, with added the email itself to show beyond the title (technical name is "Projected Field"), THIS IS A MUST (will be used in the flow)
3. "folder_name" text field for the textual value of the name of the subfolder (like "25.8.23")
its mainly used as initiate form
4. flow for filtering out excluded emails, and sending 1 by 1 a share email.
here is the "meat"
in general, the main goals are:
1. get request digest for the final HTTP requests for sharing
2. get all emails
3. filter out emails to exclude
4. send sharing emails.
I will show all the steps, with code, and comments when needed
1. "When an item is created"
[in hebrew the word "Shibolet" is the upper part of the wheat when its ready]
2. "Send an HTTP request to SharePoint" for getting the form request digest value
params:
Site Address - your site
Method - POST
Uri - _api/contextinfo
Headers:
Accept : application/json;odata=nometadata
3. "Compose" it for future use
insert into the "Add dynamic content" pane this value
body('HTTP_GET_DIGEST')?['FormDigestValue']
4. "Initialize variable" for the folder name value from "item created" for easy access
my column/SPField name is "folderNameToShare"
5. "Get folder metadata using path"
because we need the itemID of this subfolder (any folder in SPList or Library is an item behind the scenes)
use /LibraryTitle/FolderName/varFolderName from last step
[NOTE this can be annoying with adding the last slash "/", for me it didnt show, so I added to the variable, and I got an error, and then I saw IT DID added it, and then removed from the variable and it worked, so a bit annoying]
6. "Initialize variable" for the folder "itemId"
take notice this is an integer
7. "Initialize variable" for the Library Guid
I will eventually use this api "_api/web/lists('GUID')/..." just beacuse its easier for me, and its a static Library, but you can get this manually, or use ".../lists/getbytitle('SPList_Title')/..."
just open library settings and copy it from the URL
cut out the start ("%7B") and the end ("%7D") which are just url encoding for "{" and "}"
8. "Get items" for getting all emails
9. "Initialize variable" for the final emails array
no population currently, Initialize variable must be a top level (or "global", unlike "nested") step
10. "Parse JSON", to get the list of emails to exclude from the multi lookup field values
The "JSON parse" step include Content and Schema, so you can later select specific values from the content. In order to get the value for Schema MS provided a button "Generate from sample"
the Content is the Projected field, the one with "FieldName:EMAIL"
so what you need to do is:
a. use value without schema
b. run your flow (Test -> create some item)
c. after flow run, open step and copy Content value
d. back to edit mode, click "Generate from sample", paste the content, click "Done", and the schema will magically appear
FINAL:
11. "Initialize variable" for the emails to exclude array
12. "Apply to each" with "Append to array variable"
populating the emails to exclude array
you DONT need to create "Apply to each" step.
Instead create "Append to array variable" step and choose "Value" from the side pane, from the "Parse JSON" step (step 10), and he will wrap your step with a loop.
because we worked to generate a schema we can use the "Value" parameter for the SPField.
13. "Initialize variable" for the flag specific email
should we add it or exclude it from final array
[a FLAG in programming is a boolean parameter that is meant to indicate if we should start or stop doing something in the code]
14. EXCLUDING EMAILS
so when we create an item, we select some emails to exclude from the full list of emails.
THEREFOR we need a big doube-loop (nested loop) step that works like this:
[might be room for improve]
I will write/specify
- all the substeps in writing (marked as letters)
- all the substeps in big picture (marked as same letters)
- all the substeps individually (marked as same letters)
- all the substeps in writing
A. - for each item in "all emails" list (loop)
B. - get specific email
C. - set flag to true
D. - test if exclude array has items (not empty)
F. - for each item in excluded items (step 10 above) (nested loop)
G. - get specific email to exclude
H. - test if email equals to email to exclude
J. - set flag to false
K. - if flag is true
L. - add email to final list
- all the substeps in picture
- all the substeps in individually
A+B. "Apply to each" + "Compose"
you DONT need to create "Apply to each" step.
Instead create "Compose" step and choose "EMAIL" from the side pane from step 8 (get all items from all emails list) , and he will wrap your step with a loop.
C. "Set variable" flag to true
D. "Condition" is emails to exclude array NOT empty
meaning we need to test for emails to exclude
on the left hand side, in side pane click "Expression" and insert empty(variables('varArrExcludeEmails'))
make sure you set the name to the correct variable
on the right hand side, in side pane click "Expression" choose the "false" variable
and for comparison choose "is equal to"
E+F+G. "If yes" + "Apply to each" + "Compose"
you DONT need to create "Apply to each" step.
Instead, inside the "If yes" sub-step, create "Compose" step and choose "Value" from the side pane, from the "Parse JSON" step (step 10), and he will wrap your step with a loop. (just like we did in step 12)
H. "Condition" is email equal to exclude email
meaning we need to update the flag
on the left hand side, in side pane, find the Compose step where we set the current email we are testing
on the right hand side, in side pane, find the Compose step where we set the current EXCLUDE email we are testing
and for comparison choose "is equal to"
I+J. "Set variable" and set flag to false
we now "end" the 2 conditions, and the next steps (K, L) are OUTSITE these conditions steps above, see the big picture for reference.
K. "Condition" is our flag variable equals true
after all that testing, if the flag is still true, we need to add this email to the final list.
if the flag is false it means we need to exclude it (meaning SKIP adding to final list)
L. "Append to array variable" the current email to the final array
15. "Apply to each" + "Compose" + "Send an HTTP request to SharePoint"
this time, you DO need to create "Apply to each" step, and put inside the final emails array. give it a good clear name, so you can easily find it for the next step of Compose
Then create "Compose" step and choose "Current Item" from the side pane for this specific loop
and now FINALLY we can start sharing!
the next step is "Send an HTTP request to SharePoint" which is still INSIDE this last loop
"Send an HTTP request to SharePoint" contains 5 parameters, and we need to set them very carefully or we get various errors, so follow (at least until it works) letter by letter.
example errors caused just by not copying letter by letter:
- json pattern json flow is not valid
- Cannot convert a primitive value to the expected type 'Edm.Boolean'. See the inner exception for more details (can also be 'Edm.String')
the 5 parameters are:
A. Site Address (simple - your site)
B. Method (simple, POST)
C. Uri
D. Headers
E. Body
I will detail C-E
C. Uri
In steps 6+7 above we defined variables for the Library GUID and also the folder itemID.
We will now use then in our Uri value.
NOTICE the single quotes around the variables, they are MUST
the value should be like this (make sure you set your actual variables)
_api/web/lists('varLibraryGuid')/GetItemByID('varFolderId')/sharelink
D. Headers
In step 3 we stored our Digest value, here we use it
there are 3 headers:
Accept : application/json;odata=nometadata
Content-Type : application/json;odata=nometadata
X-RequestDigest : [Outputs of step 3]
THIS one was REALLY tricky and annoying. I initially tried to copy some code from some similar sources but kept getting the errors above and more.
Eventually I copied the body value from MS when sharing manually to Specific Person, View Only.
To do that, click the sharing button on your folder you want to share
and set your preferences.
Then just before hitting "Send" click F12 to open Developers Tools, and go to "Network" tab.
Then click "Send".
You will be able to see the request itself as MS makes it:
Now click on that, a new pane will open, there click on "Payload" tab, and there click on "view source" near "Request Payload" item
that will result in a long peace of raw json that we need to copy (well, I copied it for you ♥)
now will come:
1. original copied raw json text
2. picture
3. formatted text + few details
4. what you need to adjust for our flow
1. original copied raw json text
{"request":{"createLink":true,"settings":{"linkKind":6,"expiration":null,"role":1,"restrictShareMembership":true,"updatePassword":false,"password":"","scope":2,"nav":""},"peoplePickerInput":"[{\"Key\":\"arielspamtest@gmail.com\",\"DisplayText\":\"arielspamtest@gmail.com\",\"IsResolved\":true,\"Description\":\"arielspamtest@gmail.com\",\"EntityType\":\"\",\"EntityData\":{\"SPUserID\":\"arielspamtest@gmail.com\",\"Email\":\"arielspamtest@gmail.com\",\"IsBlocked\":\"False\",\"PrincipalType\":\"UNVALIDATED_EMAIL_ADDRESS\",\"AccountName\":\"arielspamtest@gmail.com\",\"SIPAddress\":\"arielspamtest@gmail.com\",\"IsBlockedOnODB\":\"False\"},\"MultipleMatches\":[],\"ProviderName\":\"\",\"ProviderDisplayName\":\"\"}]","emailData":{"body":"ef ef","subject":""}}}
2. picture
3. formatted text
{
"request":
{
"createLink":true,
"settings":{
"linkKind":6,
"expiration":null,
"role":1,
"restrictShareMembership":true,
"updatePassword":false,
"password":"",
"scope":2,
"nav":""
},
"peoplePickerInput":"[
{
\"Key\":\"arielspamtest@gmail.com\",
\"DisplayText\":\"arielspamtest@gmail.com\",
\"IsResolved\":true,
\"Description\":\"arielspamtest@gmail.com\",
\"EntityType\":\"\",
\"EntityData\":{
\"SPUserID\":\"arielspamtest@gmail.com\",
\"Email\":\"arielspamtest@gmail.com\",
\"IsBlocked\":\"False\",
\"PrincipalType\":\"UNVALIDATED_EMAIL_ADDRESS\",
\"AccountName\":\"arielspamtest@gmail.com\",
\"SIPAddress\":\"arielspamtest@gmail.com\",
\"IsBlockedOnODB\":\"False\"
},
\"MultipleMatches\":[],
\"ProviderName\":\"\",
\"ProviderDisplayName\":\"\"
}
]",
"emailData":{
"body":"here add nice message",
"subject":""
}
}
}
I tried removing some parts, always got errors.
some nice sources for code, the C# item really helped understand the request body:
some definitions:
4. what you need to adjust for our flow
just copy the formatted value and replace all instances of the email value to the value of the output inside this loop, representing an email to share with privately
Save and Test and Adjust to your needs :)
Comments
Post a Comment