How to move 10k rows X 170 Columns worth of data between sheets, while getting col C's cell ima-CodePudding

I have a dataset which containg images in col C loaded via formula =IMAGE("") and the need is to refresh the data and have these formulas load the images at destination.

I tried the Spreadsheet API, but handling the data the way it's needed it still far to me - knowledge wise.

I try with the script below, but the column C shows as blank at destination:

function getOrdersData() {
  const srcFile = SpreadsheetApp.openById('XXXXXXXXXXX');
  const srcSht = srcFile.getSheetByName('Orders');
  let srcData = srcSht.getRange(1, 1, srcSht.getLastRow(), 
  srcSht.getLastColumn()).getValues();
  const orderHeaders = srcData[4]; //Colunm headers are actually in row 05
  const imgCol = orderHeaders.indexOf('Image');//Whish is where the formulas loading the imgs are
  const imgFormulas = srcSht.getRange(1, imgCol   1, srcSht.getLastRow(), 1).getFormulas();
  
  srcData.forEach(function (row) {
    row.splice(imgCol, 1, imgFormulas);
  });
  
  const dstFile = SpreadsheetApp.openById('XXXXXXXXXXXXXXX');
  const dstSht = dstFile.getSheetByName('Orders v2');

  const dstShtLr = dstSht.getLastRow();
  if (dstShtLr > 0) {
    dstSht.getRange(1, 1, dstShtLr, dstSht.getLastColumn()).clearContent();
  }
  dstSht.getRange(1, 1, srcData.length, srcData[0].length).setValues(srcData);
}

I'd appreciate some help! Thanks in advance.

CodePudding user response：

In your script, imgFormulas is a 2-dimensional array. In this case, by srcData.forEach(,,,), srcData is not 2 dimensional array. I thought that this might be the reason for your issue. When your script is modified, how about the following modification?

From:

srcData.forEach(function (row) {
  row.splice(imgCol, 1, imgFormulas);
});

To:

srcData.forEach(function (row, i) {
  if (i > 4) row.splice(imgCol, 1, imgFormulas[i][0]);
});

if (i > 4) was used for considering Colunm headers are actually in row 05.

Note:

In your situation, when Sheets API is used, the sample script is as follows. In this case, please enable Sheets API at Advanced Google services. When the number of cells are large, this might be useful.

  function sample() {
    const srcSSId = '###'; // Please set source Spreadsheet ID.
    const dstSSId = '###'; // Please set destination Spreadsheet ID.
    const srcSheetName = 'Orders';
    const dstSheetName = 'Orders v2';
    const srcValues = Sheets.Spreadsheets.Values.get(srcSSId, srcSheetName).values;
    const srcFormulas = Sheets.Spreadsheets.Values.get(srcSSId, srcSheetName, { valueRenderOption: "FORMULA" }).values;
    const data = [{ range: dstSheetName, values: srcValues }, { range: dstSheetName, values: srcFormulas }];
    Sheets.Spreadsheets.Values.batchUpdate({ valueInputOption: "USER_ENTERED", data }, dstSSId);
  }

From:

To:

Note:

References: