Copying formula referencing a different sheet with updated cell references

0
votes

This question is similar to this one, except that the formula I am copying doesn't reference the cells in the same sheet, but reference cells in a different sheet.

For example, I have sheet 1 and 2 in a Google sheet file. For sheet 1:

A1: ='sheet 2'!A1 - 'sheet 2'!A2

B1: =' '

I would like to copy A1's formula to B1 so it looks like:

B1: ='sheet 2'!B1 - 'sheet 2'!B2

Obviously I can do this manually by dragging the + sign at the bottom right corner of A1 down to B1 in sheet 1. But how do I do this with sheet API?

I've tried the top rated solution for this question, and it works if the formula references cells in the sheet 1. If the formula references cells in sheet 2, it does copy the formula but won't update the cell reference... the result would look like: 

B1: ='sheet 2'!A1 - 'sheet 2'!A2

Any suggestions?

1
pythongoogle-sheetsgoogle-sheets-api
Use indirect() + row() - James D
@TheMaster any suggestions on tags? - Hua
Tags: It's already fixed by Ruben. Have you tried repeatCell request? - TheMaster
@TheMaster I am trying to repeat a cell from Row 3 to Row 2, but stuck on defining grid range to indicate which row is to be repeated to the rest. The startRowIndex has to be smaller than endRowIndex... Do you happen to know how to specify the row to be repeated if it's not the first row in the range definition? - Hua
repeatCell requires you provide the initial data. If the data is already present, use autofill request. Here range will be the entire range row 2:3. You can skip source and destination - TheMaster

1 Answers

1
votes

You can use autofill request to simulate autofill(i.e., dragging the + sign at the bottom right corner of A1 down)