About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

  • Home
  • Get Help
  • How Do I
  • Please Help: Creating Multiple Smartsheet Project Plans from Base Sheet Row(s) Update Trigger
Best answer

Please Help: Creating Multiple Smartsheet Project Plans from Base Sheet Row(s) Update Trigger

  • 20 August 2020
  • 6 replies
  • 338 views
K
Userlevel 1

Lengthy read, I’m happy to clarify anything I haven’t successfully articulated below.

All this is happening in with a Zap connected to Smartsheet.

I have a base sheet with columns for “Account” (customer name), “Account Number”, “Contact Status”, “Upgrade Status”, “Upgrade Date”, “Project Manager”, “Comments”, (and others not relevant).

I added a dropdown column called Upgrade Scheduled for use as a Zap trigger. It has three values: “Unscheduled”, “Scheduled”, “Created”

I'm using this base sheet to create project plans from an existing "template" sheet (not an actual Smartsheet template, but a sheet for copying)

Template has columns: “Customer”, “Account Number”, “Contact Status”, “Upgrade Status”, “Upgrade Date”, “Project Manager”, “Comments”

Trigger: When one or more rows change in the base sheet

Filter: When “Upgrade Scheduled” is equal to "Scheduled"

 

NOTE:  From here, I would prefer to create the new project plan from the existing template, then update the data in the new sheet instead of first modifying the template, but I can't do that within the same zap.  So instead, I update the template and create a new sheet from it, as follows:


Action 1: Update Row 2 in Project Plan Template with the Following:

Base sheet Columns: “Account”, “Account Number”, “Contact Status”, “Upgrade Status”, “Upgrade Date”, “Project Manager”, “Comments”

Template Columns: “Customer”, “Account Number”, “Contact Status”, “Upgrade Status”, “Upgrade Date”, “Project Manager”, “Comments”

Action 2: New Sheet from Existing Sheet in Smartsheet:

Destination: (Workspace ID) NOTE: I would like to be able to use Folder ID's within a workspace… Also, my destinations only load the root "Sheets" location, so I'm forced to use IDs

Sheet name: Value in “Customer” Column

 

NOTE:  I would also like to update the base sheet “Upgrade Scheduled” column to “Created” on each row that satisfies the filter, “Upgrade Scheduled” = “Scheduled”, so any further changes to that row don’t create a duplicate project plan… unsure how to make that happen, since Smartsheet doesn’t have Row ID Lookup functionality like Google Sheets.


Problems with this approach:

Let's say a sheet is created where the account has comments on it… cool, I get the comments in the new sheet

But if the next row processed is on an account with no comments, then the template still has the comments from the previous row… (unless I could insert blank data or an empty string… enhancement request submitted.)

Now I have project plans with comments that don't belong, until a row is processed that does have comments.

If the template could be "reset" after each row, that would solve my problem

To get around this, I decided to modify my zap so it only copies the Account field to the new plan, and all the other fields in the plan (template) are linked with VLOOKUP to the base sheet.

BUT…

Enhancement Request Please:  Cross sheet references aren't retained with "New sheet from existing sheet", so that whole plan is a failure. Highly disappointing, since in Smartsheet, "Save as new…" DOES retain cross sheet references.

 

If you’ve made it this far, thank you for hanging with me.  Any ideas or assistance accomplishing this seemingly simple task would be appreciated.

My Zap:


My Base Sheet Columns (top) and Project Plan Template Columns (bottom)
icon

Best answer by jesse 7 June 2022, 00:13

View original
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

6 replies

andywingrave
Userlevel 7
Badge +9

From here, I would prefer to create the new project plan from the existing template, then update the data in the new sheet instead of first modifying the template, but I can't do that within the same zap.  So instead, I update the template and create a new sheet from it, as follows

  • Have you thought about sending this to another zap via a webhook?

I would also like to update the base sheet “Upgrade Scheduled” column to “Created” on each row that satisfies the filter, “Upgrade Scheduled” = “Scheduled”, so any further changes to that row don’t create a duplicate project plan… unsure how to make that happen, since Smartsheet doesn’t have Row ID Lookup functionality like Google Sheets.

  • Can you not do this with a zap which triggers on an updated row?

I think that in general you are on the right path here - But I would say - Don’t be afraid, especially with your use case to set up multiple zaps to accomplish what you’re trying to do.

 

K
Userlevel 1

Hi andywingrave,

 

Thank you for your response!  To your first question, I did consider using webhooks to pass new sheet information for further processing, but there seems to be a bit of a learning curve there, not sure where to start.  If you have some thoughts for me there, I’d really appreciate it!

 

For updating the “Upgrade Scheduled” column, I was able to accomplish that in exactly the manner you describe, but then found that I could do it in the same zap, as well as add the new sheet id, result ID, to a new column I added to my trigger sheet… this was done so yet another zap could identify the new sheet for more processing… unfortunately, that didn’t work, and I’m told that’s because there’s a bug in Smartsheet preventing row data to load when I reference the sheet by pointing to the new Sheet ID column in my trigger sheet.  sigh…

 

Thanks again!

 

ksegrist

 

 

andywingrave
Userlevel 7
Badge +9

Hey there! I definitely recommend learning about webhooks - It will 10x your Zapier proficiency. It does take some time and patience, but It will be worth it. The good news is that configuring Zapier > Zapier webhooks is the easiest webhook to create: https://zapier.com/page/webhooks/

 

Regarding the bug - Was this Zapier or Smartsheet that identified it?

K
Userlevel 1

I don’t know who identified the bug, but Zapier support informed me of it and added my name to the list of people experiencing issues related to it.  Thank you for the webhook info, I’ll dig into it some more and see what I can make happen!

K
Userlevel 1

Unfortunately, sending the new sheet ID, (or the sheet name, or even the permalink) to another zap using a webhook results in the same problem… the Row ID for the sheet can’t be found.  

 

Moving on, I do see what you’re saying about using several zaps, and I’ve tried going that route.  Unfortunately since I’m processing multiple rows in my trigger sheet, I need things to happen between each row being processed.

 

**** It’s important for me to understand the order of operations when multiple rows are processed each time the zap is triggered.  *****

 

Using the flow I described above, I’m trying to “reset” the template sheet after each row is processed, but I’m getting very inconsistent results.  I even tried moving the “reset” step before my filter, with no improvement.  This is shown below, but I’ve tried resetting at several possible locations within the zap.

 

 

jesse
Userlevel 7
Badge +9

Hey folks! It’s been awhile but I wanted to circle back regarding the issues mentioned in this thread. A few things to note:

One of the issues here is a known feature request, which is the ability to retain cross-sheet reference when using `New sheet from existing sheet`. I have added this thread to the issue so as soon as we have any updates, we’ll circle back here to let the community know.

I see you had been working with a few member of our support team on this, as this appears to be a rather complex workflow. That is going to be the best place to continue working through the details of this. 

Lastly, I wanted to credit @andywingrave for his suggestions on this thread for others to try should they be building a similar workflow:

From here, I would prefer to create the new project plan from the existing template, then update the data in the new sheet instead of first modifying the template, but I can't do that within the same zap.  So instead, I update the template and create a new sheet from it, as follows

  • Have you thought about sending this to another zap via a webhook?

I would also like to update the base sheet “Upgrade Scheduled” column to “Created” on each row that satisfies the filter, “Upgrade Scheduled” = “Scheduled”, so any further changes to that row don’t create a duplicate project plan… unsure how to make that happen, since Smartsheet doesn’t have Row ID Lookup functionality like Google Sheets.

  • Can you not do this with a zap which triggers on an updated row?

I think that in general you are on the right path here - But I would say - Don’t be afraid, especially with your use case to set up multiple zaps to accomplish what you’re trying to do.

 

Hey there! I definitely recommend learning about webhooks - It will 10x your Zapier proficiency. It does take some time and patience, but It will be worth it. The good news is that configuring Zapier > Zapier webhooks is the easiest webhook to create: https://zapier.com/page/webhooks/

 

Regarding the bug - Was this Zapier or Smartsheet that identified it?

 

Terms & Conditions