Refill columns

How to reset data from columns previously deleted

1. In your Google Sheet, click "Extensions -> Apps Script"

2. Copy-Paste the code below in the Editor

3. Uncomment the name of the columns you want to refill. (remove the //)

4. Change the value of sheetName to match the name of your sheet.

5. Click Save and Run

function runRefill() {
  const columns = [
    // 'url',
    // 'emails',
    // 'phones',
    // 'socials',
    // 'fullname',
    // 'firstname',
    // 'lastname',
    // 'title',
    // 'location',
    // 'about',
    // 'skills',
  ]; // uncomment the columns you wish to "refill"
  const sheetName = 'Sheet1'; // replace with your sheet name
  refillColumns(sheetName, columns);
}


function refillColumns(sheetName, columns) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  // Get all the data in the sheet
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues(); // 2D array of all data

  // Get the header row (assumed to be the first row)
  const header = values[0]; // First row is the header

  // Map header names to their column indices
  const headerMap = {};
  for (let i = 0; i < header.length; i++) {
    headerMap[header[i]] = i;
  }

  // Find the index of the 'fullJson' column
  const fullJsonColumnIndex = headerMap['fullJson'];
  if (fullJsonColumnIndex === undefined) {
    throw new Error("Column 'fullJson' not found in the header row.");
  }

  // Iterate over each data row (skip the header row)
  for (let rowIndex = 1; rowIndex < values.length; rowIndex++) {
    const row = values[rowIndex];

    // Get the JSON data from the 'fullJson' column
    const fullJson = row[fullJsonColumnIndex];
    if (!fullJson) {
      // If there's no data in 'fullJson', skip this row
      continue;
    }

    let jsonData;
    try {
      // Parse the JSON data
      jsonData = JSON.parse(fullJson);
    } catch (e) {
      // If JSON is invalid, skip this row
      Logger.log(`Invalid JSON at row ${rowIndex + 1}: ${e}`);
      continue;
    }

    // For each column that needs to be refilled
    columns.forEach(function(columnName) {
      const columnIndex = headerMap[columnName];
      if (columnIndex === undefined) {
        // If the column doesn't exist in the sheet, skip it
        return;
      }

      let value = jsonData[columnName];
      if (value === undefined || value === null) {
        // If there's no corresponding data in JSON, skip
        return;
      }

      // If the value is an object or array, convert it to a string
      if (typeof value === 'object') {
        if (columnName === 'skills' && Array.isArray(value)) {
          value = value.map(skill => skill.name).join(', ');
        } else if ((columnName === 'emails' || columnName === 'phones' || columnName === 'socials') && Array.isArray(value)) {
          value = value.join(', ');
        }
      }

      // Update the cell value in the row
      row[columnIndex] = value;
    });

    // Update the values array with the modified row
    values[rowIndex] = row;
  }

  // Write the updated values back to the sheet
  dataRange.setValues(values);
}