Looking for a condensed version of my app script to optimize speed and efficiency

Question

Looking for a condensed version of my app script to optimize speed and efficiency

In my script, users input data and run the script by clicking a button. The script then appends the data to two different tabs and clears the data entry tab. However, I encountered an issue where I had to manually hard code each cell for appending, causing the script to search through empty rows as well. I need help optimizing the script to only target non-empty rows and append those to the next tab in order to reduce execution time. The button function must remain unchanged. Below is the script that appends data from the "Verify" tab:

function verify() {
  const mainFunctionName = "verify"; // function name Mf this function.
  const alartFunctionName = "alert4";
  const drawings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("VERIFY").getDrawings();
  const drawing = drawings.filter((e) => e.getOnAction() == mainFunctionName);
  if (drawing.length == 1) {
    drawing[0].setOnAction(alartFunctionName);
    SpreadsheetApp.flush();
   vf();
    drawing[0].setOnAction(mainFunctionName);
  } 
}
function alert4() {
  SpreadsheetApp.getUi().alert("NOW SAVING");
}

function vf() {
// Same content as before
}

javascript google-apps-script google-sheets optimization

Answer 1

Answer №1

Here's a different approach to try:

function updateValues() {
  const sheet = SpreadsheetApp.getActive();
  const verifySheet = sheet.getSheetByName("VERIFY");
  const index5Sheet = sheet.getSheetByName("INDEX5");
  const index6Sheet = sheet.getSheetByName("INDEX6");
  const range1 = ["B1", "C6", "C7", "C8", "C9"];
  const range2 = ["B10", "B11", "B1", "B2", "B3", "B4", "B5", "D6", "D7", "D8", "D9", "E9", "F9", "B12"];
  const range3 = ["M15", "M16", "M17", "M18", "M19", "M20", "M21", "M22", "M23", "M24", "M25", "M26", "M27", "M28", "M29", "M30", "M31", "M32", "M33", "M34", "M35", "M36", "M37", "M38", "M39"];
  const valuesToSet = verifySheet.getRange("A15:N39").getValues().map(([a, b, c, d, e, f, g, h, i, j, k, l, m, n]) => [a, l, b, c, d, e, f, g, h, i, j, n]);
  index5Sheet.getRange(index5Sheet.getLastRow() + 1, 1, valuesToSet.length, valuesToSet[0].length).setValues(valuesToSet);
  const updatedValues = range2.map(value => verifySheet.getRange(value).getValue());
  range1.forEach(value => verifySheet.getRange(value).clearContent());
  range3.forEach(value => verifySheet.getRange(value).clearContent());
}

Answer 2

Here's a different approach to try:

function updateValues() {
  const sheet = SpreadsheetApp.getActive();
  const verifySheet = sheet.getSheetByName("VERIFY");
  const index5Sheet = sheet.getSheetByName("INDEX5");
  const index6Sheet = sheet.getSheetByName("INDEX6");
  const range1 = ["B1", "C6", "C7", "C8", "C9"];
  const range2 = ["B10", "B11", "B1", "B2", "B3", "B4", "B5", "D6", "D7", "D8", "D9", "E9", "F9", "B12"];
  const range3 = ["M15", "M16", "M17", "M18", "M19", "M20", "M21", "M22", "M23", "M24", "M25", "M26", "M27", "M28", "M29", "M30", "M31", "M32", "M33", "M34", "M35", "M36", "M37", "M38", "M39"];
  const valuesToSet = verifySheet.getRange("A15:N39").getValues().map(([a, b, c, d, e, f, g, h, i, j, k, l, m, n]) => [a, l, b, c, d, e, f, g, h, i, j, n]);
  index5Sheet.getRange(index5Sheet.getLastRow() + 1, 1, valuesToSet.length, valuesToSet[0].length).setValues(valuesToSet);
  const updatedValues = range2.map(value => verifySheet.getRange(value).getValue());
  range1.forEach(value => verifySheet.getRange(value).clearContent());
  range3.forEach(value => verifySheet.getRange(value).clearContent());
}

Answer 3

Answer №2

This might not be shorter, but it is definitely quicker.

Instead of using multiple arrays with A1 notation for disjointed and rearranged cells, I recommend using array indices for better performance.

Fetching data using getValue() from the same sheet multiple times can significantly impact the performance of your script.

Many Google App Script beginners tend to use A1 notation for ranges, whereas I prefer using array indices. This utility function can help transition between the two methods smoothly.

My approach involves fetching all values from the sheet at once using getDataRange().getValues(). Then, by utilizing my utility function getIndices(cell), you can convert A1 notation to row and column indices, allowing you to access values swiftly and construct a row array.

The structure of my test sheet resembles the following:

https://i.stack.imgur.com/yRqZm.png

Code.gs

function testTheFunction() {
  try {
    let cells1 = ["B1","C2","D3"];
    let cells2 = ["E3","D2","C3","B2","A3"];
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let values = spread.getSheetByName("Sheet1").getDataRange().getValues();
    console.log(getArrayValues(cells1,values));
    console.log(getArrayValues(cells2,values));
  }
  catch(err) {
    console.log(err);
  }
}

/**
 * @param {string} cells[].cell - the spreadsheet cell in A1 notation
 * @param {number} values[].value - the spreadsheet cell value
 * @return {number} results[].value - an array of values
 */
function getArrayValues(cells,values) {
  try {
    let results = [];
    cells.forEach( cell => {
        let indices = getIndices(cell);
        results.push(values[indices.row][indices.col]);
      }
    );
    return results;
  }
  catch(err) {
    console.log(err);
  }
}

/**
 * @param {string} cell - the spreadsheet cell in A1 notation
 * @returns {Object} object - containing row and col
 */
function getIndices(cell) {
  try {
    let parts = cell.match(/[a-z]+/i);
    if( !parts ) throw "incorrect cell ["+cell+"]";
    parts = parts[0];
    let col = 0;
    let i = 0;
    while( i<parts.length ) {
      let char = parts.charCodeAt(i);
      col = 26*col+char-64;
      i++;
    }
    parts = cell.match(/\d+/);
    if( !parts ) throw "incorrect cell ["+cell+"]";
    row = parseInt(parts[0]);
    return { row: row-1, col: col-1 };
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

6:46:18 AM  Notice  Execution started
6:46:20 AM  Info    [ 11, 22, 33 ]
6:46:20 AM  Info    [ 43, 32, 23, 12, 3 ]
6:46:20 AM  Notice  Execution completed

Example

function vf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const pt = ss.getSheetByName("VERIFY");
  let values = pt.getDataRange().getValues();
  .
  .
  . 
  const pfv = ["A15","L15","B15","C15","D15","E15","F15","G15","H15","I15","J15","N15",];
  const pfr = getArrayValues(pfv,values);
  .
  .
  .

Answer 4

This might not be shorter, but it is definitely quicker.

Instead of using multiple arrays with A1 notation for disjointed and rearranged cells, I recommend using array indices for better performance.

Fetching data using getValue() from the same sheet multiple times can significantly impact the performance of your script.

Many Google App Script beginners tend to use A1 notation for ranges, whereas I prefer using array indices. This utility function can help transition between the two methods smoothly.

My approach involves fetching all values from the sheet at once using getDataRange().getValues(). Then, by utilizing my utility function getIndices(cell), you can convert A1 notation to row and column indices, allowing you to access values swiftly and construct a row array.

The structure of my test sheet resembles the following:

https://i.stack.imgur.com/yRqZm.png

Code.gs

function testTheFunction() {
  try {
    let cells1 = ["B1","C2","D3"];
    let cells2 = ["E3","D2","C3","B2","A3"];
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let values = spread.getSheetByName("Sheet1").getDataRange().getValues();
    console.log(getArrayValues(cells1,values));
    console.log(getArrayValues(cells2,values));
  }
  catch(err) {
    console.log(err);
  }
}

/**
 * @param {string} cells[].cell - the spreadsheet cell in A1 notation
 * @param {number} values[].value - the spreadsheet cell value
 * @return {number} results[].value - an array of values
 */
function getArrayValues(cells,values) {
  try {
    let results = [];
    cells.forEach( cell => {
        let indices = getIndices(cell);
        results.push(values[indices.row][indices.col]);
      }
    );
    return results;
  }
  catch(err) {
    console.log(err);
  }
}

/**
 * @param {string} cell - the spreadsheet cell in A1 notation
 * @returns {Object} object - containing row and col
 */
function getIndices(cell) {
  try {
    let parts = cell.match(/[a-z]+/i);
    if( !parts ) throw "incorrect cell ["+cell+"]";
    parts = parts[0];
    let col = 0;
    let i = 0;
    while( i<parts.length ) {
      let char = parts.charCodeAt(i);
      col = 26*col+char-64;
      i++;
    }
    parts = cell.match(/\d+/);
    if( !parts ) throw "incorrect cell ["+cell+"]";
    row = parseInt(parts[0]);
    return { row: row-1, col: col-1 };
  }
  catch(err) {
    console.log(err);
  }
}

Execution log

6:46:18 AM  Notice  Execution started
6:46:20 AM  Info    [ 11, 22, 33 ]
6:46:20 AM  Info    [ 43, 32, 23, 12, 3 ]
6:46:20 AM  Notice  Execution completed

Example

function vf() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const pt = ss.getSheetByName("VERIFY");
  let values = pt.getDataRange().getValues();
  .
  .
  . 
  const pfv = ["A15","L15","B15","C15","D15","E15","F15","G15","H15","I15","J15","N15",];
  const pfr = getArrayValues(pfv,values);
  .
  .
  .

Looking for a condensed version of my app script to optimize speed and efficiency

Answer №1

Answer №2

Similar questions

The React component designed to consistently render video frames onto a canvas is unfortunately incompatible with iOS devices

Swap two pairs of elements in an array in a random order

Form an item using an array

I have a specific resolution in mind where I want to run jQuery code during window scrolling

Determine which scroll bar is currently in use

Unexpected behavior with scrollTop

How can I force an element to overflow without being affected by its parent's overflow style in CSS/HTML/JS, even if the parent is

An unexpected page transition occurs when attempting to delete a link

Updating a Json array by including a new property using the Gson library

Exploring the capabilities of React testing-library for interacting with the DOM within a React application

Ways to retrieve a designated object linked to a specific value within a JavaScript structure

I am encountering a "TypeError: topics.forEach is not a function" error when attempting to retrieve metadata for topics using my kafkajs client in Node.js/express.js. Can anyone help me understand why

Implementing Knockout.js with JqueryUI Autocomplete: Access the complete object instead of just the value

HTML/JavaScript - Ways to show text entered into an input field as HTML code

Closing the video on an HTML5 player using an iPad

Nuxt and Express server are unable to receive API requests when in production mode and the /dist directory

Changing the URI in accordance with the previous URI

Is there a way to customize the color of the bar displaying my poll results?

Three.js fails to load due to Require.js issue

Error encountered with CORS in a Socket.io Express HTTP server backend