How to change undefine value to null from Sheets.Spreadsheets.Values.get() when using loop

Asking Bob 0
Viewed 36

I need help to change the undefine values from Sheets.Spreadsheets.Values.get() to null values. Because I want to use the values to make a IF condition.
The case: I want to copy and paste the data on Column 1 - 57 from other spreadsheet using Google API with some condition if the data on column 55 is not null and column 56 is null. At the end, I want to sort the value by column 57 (date). There are 60 Columns and 13k+ rows.

This is my code: (Still having a error because of the undefine value) 

function myFunction() {
 const sourceid = 'Spreadsheet ID'
 const targetid = 'Spreadsheet ID';
 const source_range = "Source File!A2:BE";
 const destination_range = "Target File!A2:BE"
 const data = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, source_range).values;
 const arr = [];
 for (let i in data) {
  if (data[i][55] !== '' && data[i][56] == '') {
   arr.push(data[i]);
  }
 }

 var values = {
  'valueInputOption': 'USER_ENTERED',
  'data': [
    {
      'range': 'Target File!A2:BE',
      'majorDimension': 'ROWS',
      'values': arr
    }
  ]
 };

 Sheets.Spreadsheets.Values.update({ values: values }, targetid, destination_range, { valueInputOption: "USER_ENTERED" });
 //sorting method (have not been made);

}
google-sheets-api google-api google-sheets google-apps-script
1 Answers

In your script, how about the following modification?

From:

const arr = [];
for (let i in data) {
 if (data[i][55] !== '' && data[i][56] == '') {
  arr.push(data[i]);
 }
}

var values = {
 'valueInputOption': 'USER_ENTERED',
 'data': [
   {
     'range': 'Target File!A2:BE',
     'majorDimension': 'ROWS',
     'values': arr
   }
 ]
};

Sheets.Spreadsheets.Values.update({ values: values }, targetid, destination_range, { valueInputOption: "USER_ENTERED" });

To:

const arr = [];
for (let i in data) {
  if (data[i][55] !== '' && !data[i][56]) {
    arr.push(data[i]);
  }
}
Sheets.Spreadsheets.Values.update({ values: arr }, targetid, destination_range, { valueInputOption: "USER_ENTERED" });

  • In this modification, !data[i][56] is used. And, the request of spreadsheets.values.update is also modified.

  • When this modification is reflected in your script, when column "BD" is not empty and the column "BE" is empty, the row is put to from row 2 of the target sheet.

Note:

  • I thought that in your situation, the following script might be able to be used instead of the for loop.

      const arr = data.filter(r => r[55] && !r[56]);

References:

Added:

About Do you know how to sort descending or ascending on my condition?, in this case, how about adding the following sample script after the line of Sheets.Spreadsheets.Values.update({ values: arr }, targetid, destination_range, { valueInputOption: "USER_ENTERED" });?

const sheet = SpreadsheetApp.openById(sourceid).getSheetByName("Source File");
Sheets.Spreadsheets.batchUpdate({ requests: [{ sortRange: { range: { sheetId: sheet.getSheetId(), startRowIndex: 1 }, sortSpecs: [{ sortOrder: "ASCENDING", dimensionIndex: 57 }] } }] }, sourceid);

  • By this script, the source sheet is sorted with ASCENDING by the column "BF".

  • If you want to use this for the target sheet, from your script, I'm worried that the target sheet has no column "BF". If you want to use this on the target sheet, please modify it as follows. But, if the target sheet has no column "BF", an error occurs. Please be careful about this.

      const sheet = SpreadsheetApp.openById(targetid).getSheetByName("Target File");
  Sheets.Spreadsheets.batchUpdate({ requests: [{ sortRange: { range: { sheetId: sheet.getSheetId(), startRowIndex: 1 }, sortSpecs: [{ sortOrder: "ASCENDING", dimensionIndex: 57 }] } }] }, targetid);
Tanaike0
Related