Extracting columns of a Google Sheet, create a new Sheet and attach to an email

[Caine_Wai]

Hi all, I am trying to set up a scenario where:

1. Watch changes or new rows on a specific column on a google sheet matching an exact value (“Yes”), once every day. Namely an approved sample request.
2. Extract values of certain columns on those rows, rearrange them into a certain order and paste them into a new google sheet. Namely sample requester’s contact information.
3. Send out that new google sheet to an email. Namely, to a warehouse to have the samples sent out.
4. Watch for changes on a specific column of the new Google Sheet. Namely updated tracking information that warehouse would be filling in.
5. Send an SMS or email to the recipient of the sample, notifying them of the tracking number (which they have provided on the original Google sheet). I suppose this would be a new scenario but similar to the first one.

What I have tried so far:
Set up a google sheet search row → table aggregator → email

blueprint.json (21.1 KB)

Problems:

• the table aggregator does not allow me to rearrange the output into a specific order. It just picks up columns I have specified as is.
• the output email’s content has no format (I am hoping for at least a table). It just has a bunch of values which becomes very difficult to read.

At this stage I haven’t tried to put the values into a new google sheet or Steps 4 and 5.

What am I missing here? Thanks in advance!

Edit: Deleted output bundles containing personal information

[samliew]

Welcome to the Make community!

1. Screenshots of module fields and filters

Please share screenshots of relevant module fields and filters in question? It would really help other community members to see what you’re looking at.

You can upload images here using the Upload icon in the text editor:

2. Scenario blueprint

Please export the scenario blueprint file to allow others to view the mappings and settings. At the bottom of the scenario editor, you can click on the three dots to find the Export Blueprint menu item.

^{(Note: Exporting your scenario will not include private information or keys to your connections)}

Uploading it here will look like this:

blueprint.json (12.3 KB)

3. And most importantly, Output bundles

Please provide the output bundles of the modules by running the scenario, then click the white speech bubble on the top-right of each module and select “Download output bundles”.

A.

Save the bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.

Uploading it here will look like this:

bundle.txt (12.3 KB)

B.

If you are unable to upload files on this forum, alternatively you can paste the formatted output bundle in this manner:

• Either add three backticks ``` before and after the code, like this:

  ```
  <sup>input/output bundle content goes here</sup>
  ```

• Or use the format code button in the editor:
  

Providing the output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

Following these steps will allow others to assist you here. Thanks!

[Caine_Wai]

Edited the original post with the added screenshots and uploads.
Sorry about that and thanks!

[samliew]

I see. Yes, Array and Table aggregators don’t allow you to rearrange the order of the keys/columns.

Since you’re building a HTML table for an email, I suggest using a “Text Aggregator” instead to build each table row, something like this, which allows you to order the incoming columns any way you like:

Then, in your email module, you insert the rest of the table’s HTML including the column headers:

[Caine_Wai]

Thanks a lot. Thats definitely points me in the right direction!

Can I then create a google sheet and paste the output of the text aggregator into it, and send the file/google sheet link via email?

[samliew]

@Caine_Wai

google sheet and paste the output of the text aggregator into it

Unfortunately not, since it’s now just a single text variable.

If you need to re-extract the data, you could try using Text Parser > “Get Content from HTML Table” module

[Caine_Wai]

I think I am getting closer to what I want
I changed to the use of iterator and it turned out to be more like it now.

blueprint (1).json (38.9 KB)

Iterator Settings:

The iterator outputted 5 separate bundles:

I then used a “create google sheet from template” module, with the template looking like this

And let it create a google sheet this way

By running it this way it has created 5 files instead, each for 1 bundle from the iterator. Each looking like this:

[
    {
        "kind": "drive#file",
        "id": "1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M",
        "name": "Sample Order - Kitchen Products2024-01-11T10:07:22.213Z",
        "mimeType": "application/vnd.google-apps.spreadsheet",
        "starred": false,
        "trashed": false,
        "explicitlyTrashed": false,
        "parents": [
            "1-Gu-Qu8QAiP7R9hYKUUgAummOC_OnrTd"
        ],
        "spaces": [
            "drive"
        ],
        "version": "6",
        "webViewLink": "https://docs.google.com/spreadsheets/d/1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M/edit?usp=drivesdk",
        "iconLink": "https://drive-thirdparty.googleusercontent.com/16/type/application/vnd.google-apps.spreadsheet",
        "hasThumbnail": true,
        "thumbnailLink": "https://lh3.googleusercontent.com/drive-storage/ANtge_Eq7KhiFMwr3TVoHYHY4lXNxlM9kNHvN6LJsvo-p77nIdcErz0wZswrkxi23hxsGuyBU0RbFWZOyg69NRGBRW6Zb4aUmWaeYq0qNg_laJsyKgCs2YraN-WB=s220",
        "thumbnailVersion": "1",
        "viewedByMe": true,
        "viewedByMeTime": "2024-01-11T09:35:11.969Z",
        "createdTime": "2024-01-11T10:07:22.262Z",
        "modifiedTime": "2024-01-11T10:07:22.262Z",
        "modifiedByMeTime": "2024-01-11T10:07:22.262Z",
        "modifiedByMe": true,
        "owners": [
            {
                "kind": "drive#user",
                "displayName": "Caine Wai",
                "photoLink": "https://lh3.googleusercontent.com/a/ACg8ocKuCr9weOsoZNQEpJ__gdQOTk65xt3IE4R0k5qS-gd3=s64",
                "me": true,
                "permissionId": "12459289486432848484",
                "emailAddress": "cevatech.hk@gmail.com"
            }
        ],
        "lastModifyingUser": {
            "kind": "drive#user",
            "displayName": "Caine Wai",
            "photoLink": "https://lh3.googleusercontent.com/a/ACg8ocKuCr9weOsoZNQEpJ__gdQOTk65xt3IE4R0k5qS-gd3=s64",
            "me": true,
            "permissionId": "12459289486432848484",
            "emailAddress": "cevatech.hk@gmail.com"
        },
        "shared": false,
        "ownedByMe": true,
        "capabilities": {
            "canAcceptOwnership": false,
            "canAddChildren": false,
            "canAddMyDriveParent": false,
            "canChangeCopyRequiresWriterPermission": true,
            "canChangeSecurityUpdateEnabled": false,
            "canChangeViewersCanCopyContent": true,
            "canComment": true,
            "canCopy": true,
            "canDelete": true,
            "canDownload": true,
            "canEdit": true,
            "canListChildren": false,
            "canModifyContent": true,
            "canModifyContentRestriction": true,
            "canModifyEditorContentRestriction": true,
            "canModifyOwnerContentRestriction": true,
            "canModifyLabels": false,
            "canMoveChildrenWithinDrive": false,
            "canMoveItemIntoTeamDrive": true,
            "canMoveItemOutOfDrive": true,
            "canMoveItemWithinDrive": true,
            "canReadLabels": false,
            "canReadRevisions": true,
            "canRemoveChildren": false,
            "canRemoveContentRestriction": false,
            "canRemoveMyDriveParent": true,
            "canRename": true,
            "canShare": true,
            "canTrash": true,
            "canUntrash": true
        },
        "viewersCanCopyContent": true,
        "copyRequiresWriterPermission": false,
        "writersCanShare": true,
        "permissions": [
            {
                "kind": "drive#permission",
                "id": "12459289486432848484",
                "type": "user",
                "emailAddress": "cevatech.hk@gmail.com",
                "role": "owner",
                "displayName": "Caine Wai",
                "photoLink": "https://lh3.googleusercontent.com/a/ACg8ocKuCr9weOsoZNQEpJ__gdQOTk65xt3IE4R0k5qS-gd3=s64",
                "deleted": false,
                "pendingOwner": false
            }
        ],
        "permissionIds": [
            "12459289486432848484"
        ],
        "size": "1024",
        "quotaBytesUsed": "1024",
        "isAppAuthorized": true,
        "exportLinks": {
            "application/x-vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M&exportFormat=ods",
            "text/tab-separated-values": "https://docs.google.com/spreadsheets/export?id=1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M&exportFormat=tsv",
            "application/pdf": "https://docs.google.com/spreadsheets/export?id=1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M&exportFormat=pdf",
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": "https://docs.google.com/spreadsheets/export?id=1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M&exportFormat=xlsx",
            "text/csv": "https://docs.google.com/spreadsheets/export?id=1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M&exportFormat=csv",
            "application/zip": "https://docs.google.com/spreadsheets/export?id=1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M&exportFormat=zip",
            "application/vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=1Lrbs9a4JJ5BGV6_TUP3urcX12Rmwm7o7tEoNX7UPJ0M&exportFormat=ods"
        },
        "linkShareMetadata": {
            "securityUpdateEligible": false,
            "securityUpdateEnabled": true
        }
    }
]

The only thing missing now is that instead of 5 separate files, I would like to have all the 5 bundles in 1 file as new rows. I can see that the values took up the cells where the tags are specified. How can I put them underneath one row after another? I know this has something to do with “add row” module, but when specifying the file name I am a bit stuck on what to do because the file should be a variable each time the scenario is run? How can I manage this?

And then the last thing is to send out this created file via email. Since it is a google sheet can it be sent as a file? Or should be it a link?
The recipient (warehouse) is supposed to look at it, make deliveries and update the tracking number on the right most column.

I definitely underestimated the complexity of this flow, but its very satisfying to be able to make progress.

Thanks again!