Unanswered

JSON to add or remove rows in Excel

(0) Share

Report

Posted on by rooftopnomad

Hi, I'm hoping someone can help me. I have a flow that converts CSV to JSON. It works great. I can then take the JSON and add a row to a table no problem. However, I would like to be able to use my CSV to JSON as my source of truth to update the excel file. Essentially, I want to be able to update or delete the row in the excel based on whether an UniqueID is present not sure how to go about building out the second part of the flow required.

Thanks,

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @rooftopnomad

Have a look at my tutorial video about how to filter Excel Rows?

https://www.youtube.com/watch?v=NsGOUJfZO7o

Thanks

Was this reply helpful? Yes No
rooftopnomad 6 on at

Like (1)

Report
hi @abm -

thanks for sharing the resource. Could you talk a little about how to best implement this into my solution? I get that I need to filter the the excel file by uniqueID but what i'm not clear on is the mechanics on how to filter the excel based on uniqueID in the JSON to update, delete, or add a row. I have three scenarios based on the UniqueID in the Excel.

If uniqueID in the excel does not exist in the JSON - delete that row
If uniqueID in the excel matches uniqueID in the JSON - update that row
If uniqueID exists in the JSON but not in the excel - add a row
hopefully that's a clear explanation on my needs.

Thanks,

Was this reply helpful? Yes No
abm abm 32,865 Most Valuable Professional on at

Like (0)

Report

Hi @rooftopnomad

Could you post a sample JSON data and excel data. I will build a solution and post soon.

Thanks

Was this reply helpful? Yes No

rooftopnomad

6 on at

Like (0)

Report

Sure @abm

Here's the JSON.

{
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "Acronym/Short Name": {
 "type": [
 "string",
 "null"
 ]
 },
 "Deliverable Name": {
 "type": [
 "string",
 "null"
 ]
 },
 "First Reviewer: Full Name": {
 "type": [
 "string",
 "null"
 ]
 },
 "Internal Due Date": {
 "type": [
 "string",
 "null"
 ]
 },
 "Date Due to Donor": {
 "type": [
 "string",
 "null"
 ]
 },
 "Comments on Deliverable": {
 "type": [
 "string",
 "null"
 ]
 },
 "Deliverable Type": {
 "type": [
 "string",
 "null"
 ]
 },
 "Deliverable Frequency": {
 "type": [
 "string",
 "null"
 ]
 },
 "Deliverables and Reports ID": {
 "type": [
 "string",
 "null"
 ]
 },
 "Country: Country Name": {
 "type": [
 "string",
 "null"
 ]
 }
 },
 "required": [
 "Acronym/Short Name",
 "Deliverable Name",
 "First Reviewer: Full Name",
 "Internal Due Date",
 "Date Due to Donor",
 "Comments on Deliverable",
 "Deliverable Type",
 "Deliverable Frequency",
 "Deliverables and Reports ID",
 "Country: Country Name"
 ]
 }
}

The excel has all the same headers as what's listed in the JSON and I want to use the "deliverables and Reports ID" to determine which ones to update, add, or delete.

Acronym/Short Name

Deliverable Name

First Reviewer: Full Name

Internal Due Date

Date Due to Donor

Comments on Deliverable

Deliverable Type

Deliverable Frequency

Deliverables and Reports ID

Country: Country Name

Thanks for taking the time to build out a solution.