How to have a reference to a sheet bound to the name instead of the sheet

1
votes

I'm making a spreadsheet to format the responses of a google form. However, this form will be given every week; ideally, I would like to import the form responses into this spreadsheet, have the formatted data sheet now use this new responses sheet instead of the older one, without having to update the formulae in the data sheet. But if I try to do this by renaming the new data sheet to what the old one was called, it keeps the old sheet reference and updates its name in all the formulae.

I haven't found any solutions from a few google searches. Is there an easy way to get around this?

EDIT: here is an example spreadsheet. Ideally, I want the data in 'Formatting Sheet' to be retrieved from 'New Sheet' instead of old sheet. However, I don't want to have to change all of the formulae in 'Formatting Sheet' to reflect this, as this will be a transition I do regularly. Unfortunately renaming the sheets preserves the reference.

1
google-apps-scriptgoogle-sheets
Can you provide a sample Spreadsheet? – Tanaike
@Tanaike I added the example sheet here, with arbitrary data to display the problem and intent. – Vedvart1
Thank you for replying and providing the sample Spreadsheet. From your shared Spreadsheet, I proposed a sample script as an answer. Could you please confirm it? If I misunderstood your question and that was not the direction you want, I apologize. – Tanaike

1 Answers

2
votes
  • You want to use the values at the sheet Formatting Sheet by replacing the values in the sheet Old Sheet with new values in the sheet New Sheet.
  • In your current issue, even when the sheet name is changed, the values cannot be updated.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

In your case, even when the sheet name is changed, the sheet ID is not changed. By this, the formula uses old values. I think that the reason of your issue is this. In this answer, in order to achieve your goal, Google Apps Script is used.

Flow:

The flow of the sample script is as follows.

  1. Retrieve the sheet object of New Sheet and Old Sheet, which are the source sheet and destination sheet, respectively.
  2. Backup Old Sheet.
    • In you don't want to backup it, please remove destinationSheet.copyTo(ss).setName(destinationSheet.getName() + "_backup_" + new Date().toISOString());.
  3. Copy the values from New Sheet to Old Sheet. By this, the values are replaced. And at Formatting Sheet, the new values are used.

Sample script:

Please copy and paste the following sample script to the script editor of your shared Spreadsheet. And run myFunction().

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("New Sheet");
  var destinationSheet = ss.getSheetByName("Old Sheet");

  // Backup "Old Sheet".
  destinationSheet.copyTo(ss).setName(destinationSheet.getName() + "_backup_" + new Date().toISOString());

  // Put values from "New Sheet" to "Old Sheet"
  sourceSheet.getDataRange().copyTo(destinationSheet.getRange(1, 1));
}
  • When you run the script of myFunction(), you can see the result calculated by new values at the sheet of Formatting Sheet.

References: