查找包含特定值的"行号"和"列"，然后将值插入该单元格

Question

描述：我想要一个脚本，它可以找到Sheet1的第#行，该行包含某个值"Cherry"；(在列B中(，然后找到包含某个值"0"的列；周期2"；(第2行(。一旦我有了那个地址(行、列(，我想插入一个值"0"；16〃；从Sheet2中的某个单元格插入到Sheet1的某个位置。

https://docs.google.com/spreadsheets/d/1z865gLweziGDwrq1uVc8WmVt4x14kZosJB7CwVG63dY/edit?usp=sharing

Answer 1

实际上我想明白了：

function Update() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets();
var dataRange = sheet[0].getDataRange();
var values = dataRange.getValues();
var rowsource = ss.getSheetByName("Budget (Micro)").getRange(6, 1).getValue()
var columnsource = ss.getSheetByName("Budget (Micro)").getRange(2, 6).getValue()
var outColumnsource = "PERIOD "+columnsource+""
Logger.log(outColumnsource)
var outRow;
for (var i = 0; i < values.length; i++)
{
if (values[i][1] == rowsource)
{
outRow = i+1;
break;
}
}

var outColumn;
for (var i = 0; i < values.length; i++)
{
if (values[54][i] == outColumnsource)
{
outColumn = i+1;
break;
}
}

var location = ss.getSheetByName("Budget (Universal)").getRange(outRow, outColumn)
var locationsource = ss.getSheetByName("Budget (Micro)").getRange(22, 17).getValue()
location.setValue(locationsource)

ss.getSheetByName("Budget (Micro)").getRangeList(['E7:E21', 'G7:G21', 'I7:I21']).clearContent()

}