how to change value in a range of cell to a data array in google AppScript?

Question

How to pull data in a range of cell and then push those values into an array of data?

In this code I tried to get those values in range E2:E97 and then push those values to an array such as [E2 value, E3 Value, etc] then set value to database sheet using dataS.getRange(dataS.getLastRow()+1).setValue(value);

but it seems I can't get it done with those code. so any idea to do this?

// Save Data

function saveData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("newform");
  var dataS = ss.getSheetByName("newdb");
  var dataBaseNameColIndex = 1;
  var formDataRange = ['E2:E97'];
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert(
    'Save Data ?',
    ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (response == ui.Button.YES) {
    for (var i = 2; i < formDataRange.length; i++) {
      var value = formS.getRange(formDataRange[i], 5).getValue();
      dataS.getRange(dataS.getLastRow() + 1).setValue(value);
      console.log(saveData);
    }
  }
}

ADW · Accepted Answer · 2021-02-18 10:38:25Z

Instead of trying to copy and paste data cell-by-cell, you may want to do it in one go. That would be more efficient.

So instead of:

  var value = formS.getRange(formDataRange[i], 5).getValue();

you could use the following without the for loop:

  var allValues = formS.getRange('E2:E97').getValues();

And then post the data to the new range like this:

  dataS.getRange(dataS.getLastRow() + 1, 2, allValues.length, allValues[0].length).setValues(value);

I am assuming you want to paste the data into column 2 onwards. Adjust the 2nd value in getRange() above accordingly.

The 3rd and 4th values in getRange() above are the number of rows and columns to pasts. allValues.length is the number of rows of data. In this case it would be 95.

and allValues[0].length would the number of columns in the top row of the data copied. And in this case it should be one.

I am suggesting this way as you won't need to keep fiddling with the number of rows and columns if you change the copy range dimensions later on.

PS:

dataS.getRange(dataS.getLastRow() + 1).setValue(value);

is missing the column number in getRange()

Collectives™ on Stack Overflow

how to change value in a range of cell to a data array in google AppScript?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related