One of my projects is making a sales spreadsheet.

The sales spreadsheet contains the names of the products and their prices are in the documentation, the challenge is getting the prices to automatically show up on the cell right next to the product name in the spreadsheet.

Here's what I did:

function Autoprice() {
  var sales = SpreadsheetApp.getActive().getSheetByName('Sales')
  var salesrow = sales.getRange('D2:D' sales.getLastRow())
  var productnames = salesrow.getValues()
  size = productnames.length 1
  for (var i = 0; i< size; i  ){
    if (productnames[i 1]=='Diary')
    {
      sales.getRange('F' i).setValue(31.90)
    }

And I just input all the prices manually. The thing is, google script does not read the sales.getRange('F' 1) as I thought it would, and I can't find the correct way to read that for every item in 'DI' cell, i want to put a price on 'FI' cell.

CodePudding user response：

You are trying to loop through a 2-dimensionall array (not technically... but each element is a single array).

So to see D2's value you would need productnames[0][0]

However, you can easily fix this using the flat() function. Modify one line of code below:

var productnames = salesrow.getValues().flat();

Also consider learning to use the debugger. If you step through your code, this is easy to see.

CodePudding user response：

Try using this script, I modified a couple of lines in the sample you shared and added comments next to it to explain.

function Autoprice() {
  var sales = SpreadsheetApp.getActive().getSheetByName('Sales')
  var salesrow = sales.getRange('D2:D' sales.getLastRow())
  var productnames = salesrow.getValues()
  size = productnames.length 1
  for (var i = 0; i< size; i  ){
    if (productnames[i]=='Diary') //If you do productnames[i 1], you're not starting from the beginning of the range, basically you're starting from D3 instead of D2
    {
      sales.getRange(i 2,6).setValue(31.90) //You can try getRange(row, column) instead
    }
  }
}

Reference: getRange(row, column)