How to replace cell with new text macro google sheets

Question

Trying to create a button to replace the contents of cell A1 - sheet1, cell E1 - sheet2 and cell E1 sheet3 with "Card_Id". Each cell contains different text and I want to align the titles as the data within them is the same. The data and titles within the sheet is replaced daily with updated data so would like to click a button and all the titles are aligned.

Tried to use this but didn't work:

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.getCurrentCell().setValue('Card_Id');
  spreadsheet.getRange('A2').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Checklist items'), true);
  spreadsheet.getRange('E1').activate();
  spreadsheet.getCurrentCell().setValue('Card_Id');
  spreadsheet.getRange('E2').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Card Updates'), true);
  spreadsheet.getCurrentCell().setValue('Card_Id');
  spreadsheet.getRange('E2').activate();
};```

Tanaike Tanaike · Accepted Answer · 2021-04-28T12:12:19

From your script, at 3rd spreadsheet.getCurrentCell().setValue('Card_Id'), the cell "A1" is activated. By this, at the sheet "Card Updates", the value is put to the cell "A1". I thought that this might be the reason of your issue.

From Trying to create a button to replace the contents of cell A1 - sheet1, cell E1 - sheet2 and cell E1 sheet3 with "Card_Id". and your script, I thought that your sheet1, sheet2 and sheet3 might be the active sheet, Checklist items and Card Updates, respectively. If my guessing is correct, how about the following modification?

Modified script:

When your script is modified, it becomes as follows.

var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A1').activate();
spreadsheet.getCurrentCell().setValue('Card_Id');
spreadsheet.getRange('A2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Checklist items'), true);
spreadsheet.getRange('E1').activate();
spreadsheet.getCurrentCell().setValue('Card_Id');
spreadsheet.getRange('E2').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Card Updates'), true);
spreadsheet.getRange('E1').activate(); // Modified
spreadsheet.getCurrentCell().setValue('Card_Id');

Or

var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A1').setValue('Card_Id');
spreadsheet.getRange("'Checklist items'!E1").setValue('Card_Id');
spreadsheet.getRange("'Card Updates'!E1").setValue('Card_Id');

If you want to put the button to other sheet, you can use the following script by setting the 1st sheet name.

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange("'Sheet1'!A1").setValue('Card_Id');
  spreadsheet.getRange("'Checklist items'!E1").setValue('Card_Id');
  spreadsheet.getRange("'Card Updates'!E1").setValue('Card_Id');

In this case, please replace the 1st sheet name from Sheet1 to your actual sheet name.

How to replace cell with new text macro google sheets

1 Answers

Modified script:

References: