如何使用Google Script返回特定单元格的值

Question

我正在尝试返回特定项目的可用数量。Google ss从响应表单中读入，并根据响应表单更新项目的数量- quantityRequested。我遇到的麻烦是，它对每个项目都更新为相同的数量。经过一些修修补补，我现在得到的结果是“未定义的”，我不知道为什么。有人知道如何显示特定项目的新值吗？我还遇到了一个错误：“你没有调用setValue的权限(167行)”--有什么建议吗？

 function updateQuantity(){

  var app = SpreadsheetApp;
  var activeSheet = app.getActiveSpreadsheet().getSheetByName("Inventory");

  //add/remove items and their quantity as necessary. Remember to edit any 'else if' statement 
  var Photoshop_2D = 5;
  var Illustrator_2D = 5;
  var Premiere_Pro = 5;
  var After_Effects = 5;
  var Photoshop_3D = 5;
  var Illustrator_3D = 5;
  var Tinkercad = 5;
  var Silhouette_Studio = 5;
  var iMovie = 5;
  var Garage_Band = 5;
  var Vinyl_Cutter = 5;
  var Printer_3D = 5;
  var Headphones = 5;
  var Microphone = 5;
  var DSLR_Camera = 5;
  var GoPro = 5;
  var Handy_Recorder = 5;
  var Boom_Mic = 5;
  var Tripod = 5;
  var Makey_Makey = 5;
  var MacBook_Air = 5;
  var iPad_Air = 5;
  var Chromebook = 5;
  var Chromecast = 5;



  //i is current row index
  for (var i = 2; i<=25; i++) {
    for (var x = 2; x<=25; x++) {

    var quantityRequest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inventory").getRange("H2").getValue();
    var productName = activeSheet.getRange(2, 7).getValue();
    var item = activeSheet.getRange(i, 2);


    if (productName == "Photoshop 2D" && item == "Photoshop 2D") {
      var quantity = (Photoshop_2D - quantityRequest);
      break;
    } 
  else if (productName == "Illustrator 2D" && item == "Illustrator 2D") {
    quantity = (Illustrator_2D - quantityRequest);
    break;
    }  
  else if (productName == "Premiere Pro" && item == "Premiere Pro") {
    quantity = (Premiere_Pro - quantityRequest);
    break;
    }
  else if (productName == "Photoshop 3D" && item == "Photoshop 3D") {
    quantity = (Photoshop_3D - quantityRequest);
    break;
    }
  else if (productName == "Illustrator 3D" && item == "Illustrator 3D") {
    quantity = (Illustrator_3D - quantityRequest);
    break;
    }
  else if (productName == "Tinkercad" && item == "Tinkercad") {
    quantity = (Tinkercad - quantityRequest);
    break;
    } 
  else if (productName == "Silhouette Studio" && item == "Silhouette Studio") {
    quantity = (Silhouette_Studio - quantityRequest);
    break;
    }
  else if (productName == "iMovie" && item == "iMovie") {
   quantity = (iMovie - quantityRequest);
    break;
    }    
  else if (productName == "Garage Band" && item == "Garage Band") {
    quantity = (Garage_Band - quantityRequest);
    break;
    }    
  else if (productName == "Vinyl Cutter" && item == "Vinyl Cutter") {
    quantity = (Vinyl_Cutter - quantityRequest);
    break;
    }    
  else if (productName == "3D Printer" && item == "3D Printer") {
    quantity = (Printer_3D - quantityRequest);
    break;
    }
  else if (productName == "Headphones" && item == "Headphones") {
    quantity = (Headphones - quantityRequest);
    break;
    }    
  else if (productName == "Microphone" && item == "Microphone") {
    quantity = (Microphone - quantityRequest);
    break;
    }  
  else if (productName == "DSLR Camera" && item == "DSLR Camera") {
    quantity = (DSLR_Camera - quantityRequest);
    break;
    }    
  else if (productName == "GoPro" && item == "GoPro") {
    quantity = (GoPro - quantityRequest);
    break;
    }    
  else if (productName == "Handy Recorder" && item == "Handy Recorder") {
    quantity = (Handy_Recorder - quantityRequest);
    break;
    }    
  else if (productName == "Boom Mic" && item == "Boom Mic") {
    quantity = (Boom_Mic - quantityRequest);
    break;
    }    
  else if (productName == "Tripod" && item == "Tripod") {
    quantity = (Tripod - quantityRequest);
    break;
    }    
  else if (productName == "Makey Makey" && item == "Makey Makey") {
    quantity = (Makey_Makey - quantityRequest);
    break;
    }
  else if (productName == "MacBook Air" && item == "MacBook Air") {
    quantity = (MacBook_Air - quantityRequest);
    break;
    }
  else if (productName == "iPad Air" && item == "iPad Air") {
    quantity = (iPad_Air - quantityRequest);
    break;
    }    
  else if (productName == "Chromebook" && item == "Chromebook") {
    quantity = (Chromebook - quantityRequest);
    break;
    }    
  else if (productName == "Chromecast" && item == "Chromecast") {
    quantity = (Chromecast - quantityRequest);
    break;
    } 
  }
  return activeSheet.getRange(x, 3).setValue(quantity);
  }
}

Answer 1

你可以参考这些线程：1和2。使用getValues方法，该方法返回此范围的值的矩形网格。示例：var title = SpreadsheetApp.getActiveSheet().getRange(lastRow, 2).getValue();