How to automate putting rows (based on conditions) of one sheet to a second sheet?

Hi,

My sheet 1 has a structure with column 1, 2, and 3.

My sheet 2 has a structure with the same columns as sheet 1 - column 1, 2, and 3 - plus new columns column 4 and 5.

Based on values in column 2 in sheet 1, I would like to export the row in sheet 1 that satisfies a condition, to sheet 2. Once sheet 2 is updated, I will put in new values in columns 4 and 5.

Sheet 1 has many rows, and cell linking becomes tedious if I have to set cell linking for these rows to export to Sheet 2. I also cannot use the Report builder, since I need to be able to put in values in new columns in Sheet 2.

How do I automate this requirement?

Thanks in advance,

Dan

Find more posts tagged with

Sheets

Comments

Tony Coleman

Hi Dan, it sounds like you'd need to use a middleware type of app called Zapier or Azuqua (using API's) to build a workflow to move/copy data from sheet 1 based on a trigger (the condition you mentioned).

The only other way would be cell linking, unless you fall back to having this all in only 1 sheet. I can't think of a more elegant solution than

1 - Zapier/Azuqua (or potentially your own Python skills)

2 - Carry on with Cell linking

3 - Fallback to using 1 sheet, depending on the reason you want to go with the 2 sheet response?

Regards

Marcus Odum

I agree with Tony. You can leverage some middleware or create a small application, or use filters and permissions under one sheet. Are you adding rows to the second sheet as well? Are others using it with restricted views?

I would use 1 sheet (all columns: 4 and 5 locked for admins) with 2 reports. Report 1 will have columns 1,2,3. Report 2 will have 1,2,3,4,5. The report criteria would be based on the row 2 condition on your sheet.

DanS

Thanks for your comments. I'm afraid I can't put all in one sheet, though. The 2 sheets are that way because there are also distinct owners per sheet. It would be very unattractive a solution to tell the owners that they need to share a single sheet.

The Smartsheet API for Python SDK seems the way to go.

Zapier, I've inspected. Unfortunately the Smartsheet hook needs the premium (i.e. paid) version.

CanadaJim

Could you just copy/link Sheet 1 to Sheet 2, but set a filter on Sheet 2 to hide the ones you don't want?

DanS

That could work, but not too ideal, as Sheet 1 and Sheet 2 have different owners, and knowing exactly what row to copy/link at any time is variable, and could involve many rows at a single time.

I have made baby steps on the Smartsheet API, but still nowhere near the objective. I'm thinking to inspect how cell-linking is represented in the API.

Guilherme Ribeiro

I know it's been a while but I have the same roadblock... Did you had success using the Python API? If so would you kindly share documentation and links on how to do it?

DanS

Yes indeed, it has been quite a while. I'm trying to recall what exactly was my need on this thread, but cannot yet remember exactly what it was.

However, I did get to use Python API for Smartsheet very recently to push rows in a local Excel file to a Smartsheet file.

If you can provide me details on your requirement, I'll be happy to help you out

Guilherme Ribeiro

hi Dan,

here it´s your thread statup subject:

"...

My sheet 1 has a structure with column 1, 2, and 3.

My sheet 2 has a structure with the same columns as sheet 1 - column 1, 2, and 3 - plus new columns column 4 and 5.

Based on values in column 2 in sheet 1, I would like to export the row in sheet 1 that satisfies a condition, to sheet 2. Once sheet 2 is updated, I will put in new values in columns 4 and 5.

How do I automate this requirement?

Thanks in advance,

Dan

..."

Did you have any success using Python API or copy/link Sheet 1 to Sheet 2, but set a filter on Sheet 2 to hide the ones you don't want, as suggested by Canadajim and Tony respectively

DanS

yes, I had success exporting rows from one sheet to another using the API.

I was trying to post the python code here but only pictures are allowed to be uploaded.

screenshots of the 2 sheets here, both sheets have a Task List structure.

On Sheet 1, if the Done cell of a row is checked, that will be a candidate to export to Sheet 2.

On Sheet 2, the ID, Task Name, Due Date, and Assigned To will be imported. Then the Archived column will be updated for the date when the row was imported.

Here's the source code.

Screenshot from 2018-04-18 06-06-09.png

Screenshot from 2018-04-18 06-07-11.png