Utiliser JSON pour transmettre des données vers et à partir de scripts Office

JSON (JavaScript Object Notation) est un format de stockage et de transfert de données. Chaque objet JSON est une collection de paires nom/valeur qui peuvent être définies lors de la création. JSON est utile avec les scripts Office, car il peut gérer la complexité arbitraire des plages, des tableaux et d’autres modèles de données dans Excel. JSON vous permet d’analyser les données entrantes à partir de services web et de transmettre des objets complexes via des flux Power Automate.

Cet article se concentre sur l’utilisation de JSON avec des scripts Office. Nous vous recommandons d’abord d’en savoir plus sur le format à partir d’articles tels que Json Introduction de W3 Schools.

Analyser des données JSON dans une plage ou une table

Les tableaux d’objets JSON fournissent un moyen cohérent de transmettre des lignes de données de table entre les applications et les services web. Dans ce cas, chaque objet JSON représente une ligne, tandis que les propriétés représentent les colonnes. Un script Office peut effectuer une boucle sur un tableau JSON et le réassembler en tant que tableau 2D. Ce tableau est ensuite défini en tant que valeurs d’une plage et stocké dans un classeur. Les noms des propriétés peuvent également être ajoutés en tant qu’en-têtes pour créer une table.

Le script suivant montre les données JSON en cours de conversion en table. Notez que les données ne proviennent pas d’une source externe. Cela est abordé plus loin dans cet article.

/**
 * Sample JSON data. This would be replaced by external calls or
 * parameters getting data from Power Automate in a production script.
 */
const jsonData = [
  { "Action": "Edit", /* Action property with value of "Edit". */
    "N": 3370, /* N property with value of 3370. */
    "Percent": 17.85 /* Percent property with value of 17.85. */
  },
  // The rest of the object entries follow the same pattern.
  { "Action": "Paste", "N": 1171, "Percent": 6.2 },
  { "Action": "Clear", "N": 599, "Percent": 3.17 },
  { "Action": "Insert", "N": 352, "Percent": 1.86 },
  { "Action": "Delete", "N": 350, "Percent": 1.85 },
  { "Action": "Refresh", "N": 314, "Percent": 1.66 },
  { "Action": "Fill", "N": 286, "Percent": 1.51 },
];

/**
 * This script converts JSON data to an Excel table.
 */
function main(workbook: ExcelScript.Workbook) {
  // Create a new worksheet to store the imported data.
  const newSheet = workbook.addWorksheet();
  newSheet.activate();

  // Determine the data's shape by getting the properties in one object.
  // This assumes all the JSON objects have the same properties.
  const columnNames = getPropertiesFromJson(jsonData[0]);

  // Create the table headers using the property names.
  const headerRange = newSheet.getRangeByIndexes(0, 0, 1, columnNames.length);
  headerRange.setValues([columnNames]);

  // Create a new table with the headers.
  const newTable = newSheet.addTable(headerRange, true);

  // Add each object in the array of JSON objects to the table.
  const tableValues = jsonData.map(row => convertJsonToRow(row));
  newTable.addRows(-1, tableValues);
}

/**
 * This function turns a JSON object into an array to be used as a table row.
 */
function convertJsonToRow(obj: object) {
  const array: (string | number)[] = [];

  // Loop over each property and get the value. Their order will be the same as the column headers.
  for (let value in obj) {
    array.push(obj[value]);
  }
  return array;
}

/**
 * This function gets the property names from a single JSON object.
 */
function getPropertiesFromJson(obj: object) {
  const propertyArray: string[] = [];
  
  // Loop over each property in the object and store the property name in an array.
  for (let property in obj) {
    propertyArray.push(property);
  }

  return propertyArray;
}

Conseil

Si vous connaissez la structure du JSON, vous pouvez créer votre propre interface pour faciliter l’obtention de propriétés spécifiques. Vous pouvez remplacer les étapes de conversion JSON en tableau par des références de type sécurisé. L’extrait de code suivant montre ces étapes (désormais commentées) remplacées par des appels qui utilisent une nouvelle ActionRow interface. Notez que la fonction n’est convertJsonToRow plus nécessaire.

  // const tableValues = jsonData.map(row => convertJsonToRow(row));
  // newTable.addRows(-1, tableValues);
  // }

     const actionRows: ActionRow[] = jsonData as ActionRow[];
     // Add each object in the array of JSON objects to the table.
     const tableValues = actionRows.map(row => [row.Action, row.N, row.Percent]);
     newTable.addRows(-1, tableValues);
   }
   
   interface ActionRow {
     Action: string;
     N: number;
     Percent: number;
   }

Obtenir des données JSON à partir de sources externes

Il existe deux façons d’importer des données JSON dans votre classeur par le biais d’un script Office.

• En tant que paramètre avec un flux Power Automate.
• Avec un fetch appel à un service web externe.

Modifier l’exemple pour qu’il fonctionne avec Power Automate

Les données JSON dans Power Automate peuvent être passées en tant que tableau d’objets génériques. Ajoutez une object[] propriété au script pour accepter ces données.

// For Power Automate, replace the main signature in the previous sample with this one
// and remove the sample data.
function main(workbook: ExcelScript.Workbook, jsonData: object[]) {

Vous verrez ensuite une option dans le connecteur Power Automate à ajouter jsonData à l’action Exécuter le script .

Modifier l’exemple pour utiliser un fetch appel

Les services web peuvent répondre aux fetch appels avec des données JSON. Cela donne à votre script les données dont il a besoin tout en vous conservant dans Excel. Pour en savoir plus sur fetch et les appels externes, consultez Prise en charge des appels d’API externes dans Scripts Office.

// For external services, replace the main signature in the previous sample with this one,
// add the fetch call, and remove the sample data.
async function main(workbook: ExcelScript.Workbook) {
  // Replace WEB_SERVICE_URL with the URL of whatever service you need to call.
  const response = await fetch('WEB_SERVICE_URL');
  const jsonData: object[] = await response.json();

Créer du code JSON à partir d’une plage

Les lignes et les colonnes d’une feuille de calcul impliquent souvent des relations entre leurs valeurs de données. Une ligne d’une table correspond conceptuellement à un objet de programmation, chaque colonne étant une propriété de cet objet. Considérez la table de données suivante. Chaque ligne représente une transaction enregistrée dans la feuille de calcul.

ID	Date	Montant	Fournisseur
1	6/1/2022	43,54 $	Le meilleur pour vous Organics Company
2	6/3/2022	67,23 $	Liberty Bakery et Café
3	6/3/2022	37,12 $	Le meilleur pour vous Organics Company
4	6/6/2022	86,95 $	Coho Vineyard
5	6/7/2022	13,64 $	Liberty Bakery et Café

Chaque transaction (chaque ligne) est associée à un ensemble de propriétés : « ID », « Date », « Amount » et « Vendor ». Cela peut être modélisé dans un script Office en tant qu’objet.

// An interface that wraps transaction details as JSON.
interface Transaction {
  "ID": string;
  "Date": number;
  "Amount": number;
  "Vendor": string;
}

Les lignes de l’exemple de table correspondent aux propriétés de l’interface, de sorte qu’un script peut facilement convertir chaque ligne en objet Transaction . Cela est utile lors de la sortie des données pour Power Automate. Le script suivant itère sur chaque ligne de la table et l’ajoute à un Transaction[].

function main(workbook: ExcelScript.Workbook) {
  // Get the table on the current worksheet.
  const table = workbook.getActiveWorksheet().getTables()[0];

  // Create an array of Transactions and add each row to it.
  let transactions: Transaction[] = [];
  const dataValues = table.getRangeBetweenHeaderAndTotal().getValues();
  for (let i = 0; i < dataValues.length; i++) {
    let row = dataValues[i];
    let currentTransaction: Transaction = {
      ID: row[table.getColumnByName("ID").getIndex()] as string,
      Date: row[table.getColumnByName("Date").getIndex()] as number,
      Amount: row[table.getColumnByName("Amount").getIndex()] as number,
      Vendor: row[table.getColumnByName("Vendor").getIndex()] as string
    };
    transactions.push(currentTransaction);
  }

  // Do something with the Transaction objects, such as return them to a Power Automate flow.
  console.log(transactions);
}

// An interface that wraps transaction details as JSON.
interface Transaction {
  "ID": string;
  "Date": number;
  "Amount": number;
  "Vendor": string;
}

Exporter JSON avec fetch

Tout comme l’importation de données avec fetch, vous pouvez envoyer des données à partir de votre classeur avec une commande similaire. Une POST commande prend toutes les données JSON stringifiées et les envoie au point de terminaison spécifié.

Pour voir cela en action, remplacez la console.log(transactions); ligne dans l’exemple précédent par le code suivant. Cela émet une POST commande sur un serveur de test, puis lit les données.

  const response = await fetch('https://jsonplaceholder.typicode.com/posts', {
    method: 'POST',
    body: JSON.stringify(transactions),
    headers: {
      'Content-type': 'application/json; charset=UTF-8',
    },
  });
  const jsonData: object[] = await response.json();
  console.log(jsonData);

Utiliser un objet générique

L’exemple précédent suppose que les valeurs d’en-tête de tableau sont cohérentes. Si votre table comporte des colonnes variables, vous devez créer un objet JSON générique. Le script suivant montre un script qui journalise n’importe quelle table au format JSON.

function main(workbook: ExcelScript.Workbook) {
  // Get the table on the current worksheet.
  const table = workbook.getActiveWorksheet().getTables()[0];

  // Use the table header names as JSON properties.
  const tableHeaders = table.getHeaderRowRange().getValues()[0] as string[];
  
  // Get each data row in the table.
  const dataValues = table.getRangeBetweenHeaderAndTotal().getValues();
  let jsonArray: object[] = [];

  // For each row, create a JSON object and assign each property to it based on the table headers.
  for (let i = 0; i < dataValues.length; i++) {
    // Create a blank generic JSON object.
    let jsonObject: { [key: string]: string } = {};
    for (let j = 0; j < dataValues[i].length; j++) {
      jsonObject[tableHeaders[j]] = dataValues[i][j] as string;
    }

    jsonArray.push(jsonObject);
  }

  // Do something with the objects, such as return them to a Power Automate flow.
  console.log(jsonArray);
}

Voir aussi

• Prise en charge des appels d’API externes dans Scripts Office
• Exemple : utiliser des appels de récupération externes dans les scripts Office
• Exécuter des scripts Office avec Power Automate