Requirements:

  • Install Pyzo or any interface to run Python script : https://pyzo.org/

  • You need to have 1 folder named Workflow with:
    - Mapping file following this template where you do the mapping between the old and the new fields

Mapping.xlsx

- 1 Folder named Workflows where you put the json files for the dashboards you want to replicate
- 1 Folder named New Workflows where you will get the new json files for your updated dashboards

  • You need to change the following path in order to run the script. It should be the path of the folder containing the Workflows folder
    /Users/apollinehirschy/Desktop/Workflow

If you are on Windows, you might need to duplicate the backlashes:
Users\\apolline\\Desktop\\Folder\\Sub Folder

  • You need to change total_nh.xls into the filename you want to get

  • If its the first time you are using Pyzo, install pandas by writing install pandas in the shell and clicking on enter
    Same with : install xlrd, install os, install json

  • Run this script

import pandas as pd
import os
from os.path  import basename
import xlrd
import json


# Initialisation:
# 1. Create a folder where you put the script and the json file you have downloaded from the webapp
# 2. Change the path of the different files and if necessary, the names of the different files

adresse_export = "/Users/apollinehirschy/Desktop/Workflow/Workflows/"

j=0

with os.scandir(adresse_export) as entries:

    for entry in entries:

        j=j+1



        value=entry.name

        filename='/Users/apollinehirschy/Desktop/Workflow/Workflows/' + value
        new_filename='/Users/apollinehirschy/Desktop/Workflow/New Workflows/new_data' + str(j) + '.json'

        # FILE A / Please put the path & the name of the json file you have downloaded from the webapp
        if value != '.DS_Store':

            with open(filename, 'r') as file:

                data = json.load(file)
                file.close()

                # FILE B / Please put the path & the name of the excel file you have used to do the mapping between the old fields and the new fields
                # The mapping file should always have the same structure : COLUMN A = old values to be replaced, COLUMN B = new values

                document = xlrd.open_workbook('/Users/apollinehirschy/Desktop/Workflow/Mapping.xlsx')
                queries = document.sheet_by_index(0)
                nb_row = str(queries.nrows)

            for i in range(1,int(nb_row)):

                old_value = queries.cell_value(rowx=i, colx=0)
                new_value= queries.cell_value(rowx=i, colx=1)

                data = json.loads(json.dumps(data).replace(old_value,new_value))

            settings = document.sheet_by_index(1)
            nb_row = str(settings.nrows)

            for i in range(1,int(nb_row)):

                old_value = '"' + settings.cell_value(rowx=i, colx=0) + '"'
                new_value= '"'  + settings.cell_value(rowx=i, colx=1) + '"'

                data = json.loads(json.dumps(data).replace(old_value,new_value))

            # FILE C / Please put the path & the name you want to give to the new json file you will upload on the webapp
            # Once the script is done, you will find the file with this name in the folder you have indicated in the path
            with open(new_filename, 'w') as f:
                json.dump(data, f)

        print(j)



print("You can now upload the new workflows on the webapp. You have " + str(j) + " files to upload.")
CODE