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

Using RxJS v5 for Sending a POST Request with Parameters

Send the user to the login page once their email has been verified

Link a distinctive number to a specific element

The power of relative URLs in AJAX calls

unable to locate the PHP file on the server

AngularJS: Utilizing bold text to enhance the separation of concerns between controllers and templates

Tips for bringing a particular tab into focus when a page initially loads

Should front-end and back-end share Typescript data modeling through classes or interfaces?

Navigating through nested JSON objects in React to display data effectively

A capability that operates on an array of pairs as its parameter, where the primary component of each pair signifies the superior category of the secondary

Troubleshooting Bootstrap bug caused by rollupPluginBabelHelpers

Managing the position of the caret within a content-editable div

Cutting-edge framework for Single Page Applications

Implementing relative pathing in front-end development while using ExpressJS for the back-end

Empty the localStorage when terminating the IE process using the Task Manager

Tips on avoiding quotation marks in a Less variable containing a color identifier

Tips for incorporating an onClick event into a variable beyond the class extension

Aurelia's navigation feature adds "?id=5" to the URL instead of "/5"

How to Refine Database Queries in Laravel Without Using Conditional Statements

Retrieve a JSON array using an HTTP Get request in JavaScript (with jQuery)