Tag: Spreadsheet Optimization

// Example of data
const data = [{
  "range": "A35",
  "values": [["TEST"]]
}];

/**
 * Write to multiple, disjoint data ranges.
 * @param {string} spreadsheetId The spreadsheet ID to write to.
 * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate
 */
function writeToMultipleRanges(spreadsheetId, data, sheetName) {

  let ssGetUrl = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}`
  let options = {
    muteHttpExceptions: false,
    contentType: 'application/json',
    method: 'get',
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() }
  };
  let ssGetresponse = JSON.parse(UrlFetchApp.fetch(ssGetUrl, options));
  let sheets = ssGetresponse.sheets;
  let rowCount = 0;
  let sheetId = 0;
  sheets.forEach(sheet => {
    if (sheet.properties.title == sheetName) {
      rowCount = sheet.properties.gridProperties.rowCount
      sheetId = sheet.properties.sheetId;
    }
  });

  let num = parseInt(String(data[0]["range"]).split("!")[1].replace(/[^0-9]/g, ''));
  if (rowCount < num) {
    let diff = num - rowCount;
    let resource = {
      "requests": [
        {
          "appendDimension": {
            "length": diff,
            "dimension": "ROWS",
            "sheetId": sheetId
          }
        }
      ]
    };
    let ssBatchUpdateUrl = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}:batchUpdate`
    let options = {
      muteHttpExceptions: true,
      contentType: 'application/json',
      method: 'post',
      payload: JSON.stringify(resource),
      headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() }
    };
    let response = JSON.parse(UrlFetchApp.fetch(ssBatchUpdateUrl, options));
  }

  const request = {
    'valueInputOption': 'USER_ENTERED',
    'data': data
  };

  try {
    const response = Sheets.Spreadsheets.Values.batchUpdate(request, spreadsheetId);
    if (response) {
      return;
    }
    console.log('response null');
  } catch (e) {
    console.log('Failed with error %s', e.message);
  }
}

/**
 * Converts a row and column index to A1 notation (e.g., 3, 5 → "E3").
 *
 * @param {number} rowIndex - The 1-based row index.
 * @param {number} colIndex - The 1-based column index.
 * @returns {string} The A1 notation corresponding to the given indices.
 */
function getA1Notation(rowIndex, colIndex) {
  if (rowIndex < 1 || colIndex < 1) {
    throw new Error("Both rowIndex and colIndex must be greater than or equal to 1.");
  }
  let columnLabel = "";
  let col = colIndex;
  while (col > 0) {
    const remainder = (col - 1) % 26;
    columnLabel = String.fromCharCode(65 + remainder) + columnLabel;
    col = Math.floor((col - 1) / 26);
  }
  return columnLabel + rowIndex;
}

/**
 * 
 */
function Main_Update_Formula_Data(ssId, sheet) {

  // Get formulas in first row
  const formulas = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getFormulas()[0];

  // Get last row index
  const lastRowIndex = sheet.getLastRow();

  // Prepare ranges to treat
  const ranges = [];

  // Loop through formulas to expand until the end
  for (let i = 0; i < formulas.length; i++) {

    if (formulas[i] != ""
      && !String(formulas[i]).startsWith("=arrayformula")
      && !String(formulas[i]).startsWith("=ARRAYFORMULA")
      && !String(formulas[i]).startsWith("=ArrayFormula")) {

      const thisRange = {
        startRowIndex: 1,
        endRowIndex: lastRowIndex - 1,
        startColumnIndex: i,
        endColumnIndex: i + 1,
        formulas: [formulas[i]],
      };

      ranges.push(thisRange);
    }
  }

  // Inject and freeze data except first row of formulas
  injectAndFreezeFormulas(ssId, sheet.getSheetId(), sheet.getName(), ranges);
}


/**
 * Injects formulas into multiple ranges, waits for computation, then replaces them with static values.
 * @param {string} spreadsheetId - Spreadsheet ID.
 * @param {number} sheetId - Numeric sheet ID.
 * @param {string} sheetName - Sheet name (for value reads/writes).
 * @param {Array<Object>} ranges - Array of range configs like:
 * @return {void}
 */
function injectAndFreezeFormulas(spreadsheetId, sheetId, sheetName, ranges) {
  // --- Inject formulas via batchUpdate
  const requests = [];
  ranges.forEach(rangeDef => {
    const {
      startRowIndex,
      endRowIndex,
      startColumnIndex,
      endColumnIndex,
      formulas,
    } = rangeDef;

    formulas.forEach((formula, idx) => {
      requests.push({
        repeatCell: {
          range: {
            sheetId,
            startRowIndex,
            endRowIndex,
            startColumnIndex: startColumnIndex + idx,
            endColumnIndex: startColumnIndex + idx + 1,
          },
          cell: {
            userEnteredValue: { formulaValue: formula },
          },
          fields: 'userEnteredValue',
        },
      });
    });
  });

  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
  Logger.log(`Formulas injected into ${ranges.length} range(s)`);

  // --- Wait briefly to ensure calculations are done
  Utilities.sleep(1000);

  // --- Read computed values via API (valueRenderOption: UNFORMATTED_VALUE)
  const readRanges = ranges.map(r =>
    `${sheetName}!${columnLetter(r.startColumnIndex + 1)}3:` +
    `${columnLetter(r.endColumnIndex)}${r.endRowIndex + 1}`
  );

  const readResponse = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, {
    ranges: readRanges,
    valueRenderOption: 'UNFORMATTED_VALUE',
  });

  // --- Reinject values (overwrite formulas)
  const writeData = readResponse.valueRanges.map((vr, idx) => ({
    range: readRanges[idx],
    values: vr.values || [],
  }));

  Sheets.Spreadsheets.Values.batchUpdate(
    {
      valueInputOption: 'RAW',
      data: writeData,
    },
    spreadsheetId
  );

  Logger.log(`Formulas replaced by static values in ${writeData.length} range(s)`);
}

/**
 * Helper — Convert a column index (1-based) to A1 notation.
 *
 * @param {number} colIndex - 1-based column index.
 * @return {string}
 */
function columnLetter(colIndex) {
  let letter = '';
  while (colIndex > 0) {
    const remainder = (colIndex - 1) % 26;
    letter = String.fromCharCode(65 + remainder) + letter;
    colIndex = Math.floor((colIndex - 1) / 26);
  }
  return letter;
}