I would like to replace the GAS code with the SpreadSheet cell formula IMPORTRANGE and paste it as a value.

As stated in the title, if the data is not very wide, the code below will work.

If the A1 cell contains an IMPORTRANGE formula,
By taking the value of all cells from there and setting it to
A1 cells become values and all other cells remain.

const values=sheet.getRange(
    1,1, sheet.getLastRow(), sheet.getLastColumn()
  ) .getValues();

  sheet.getRange(
    1,1, sheet.getLastRow(), sheet.getLastColumn()
  .setValues(values);

However, if it becomes a larger seat, it gets stuck in the 6 minute limit in the middle of setValues. All of the data will no longer remain in the cell.

Keep everything you can get from the IMPORTRANGE formula for A1 cells at faster or easier GAS. Is it possible to erase the formula for A1 cells?

If you simply leave the A1 cell blank, of course all the values that IMPORTRANGE had will disappear.

I look forward to your kind cooperation.

google-apps-script google-spreadsheet

2023-01-05 22:53

1 Answers

Self-resolved.

SetValues is surprisingly slow and times out if it's tens of thousands of rows. The process was completed in a few minutes by doing the following:

sheets into backup sheets.Of course, the IMPORTRANGE part will be copied.
Delete IMPORTRANGE formula on original sheet (all data on that sheet will disappear)
Range.copyTo(range) from the backup sheet to the original sheet.You can specify a numeric copy.
Delete backup sheet

In this way, what was displayed in IMPORTRANGE can be retrieved as a value by IMPORTRANGE.
This will take about 2,3,4 minutes, but it's done within the operating range.

If there is a better way, I would appreciate it if you could write it down.I look forward to your kind cooperation.

2023-01-05 23:02

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656